rPman

Для того чтобы сравнивать данные, вам нужно привести их к общему виду.

В вашем случае это не точки а связный двунаправленный граф, где вершины - города, а ветви - дороги. Соответственно вы ваши загружаемые точки должны привести к этому же вижу - подграфу, сначала поиском точек (вычисляя расстояние между искомой точкой и точкой города, определив минимальное).

Если это критично, так же можете попытаться искать попадание точек на дороги (но это в общем случае невозможно, дороги ведь в реальности не прямые, а в базе у вас только точки городов).

И само собой, точки в вашем запросе как то должны быть связаны, направление движения или хотя бы через ближайшие, чтобы вы могли различать из какого города и куда вы двигаетесь.

получив граф, вы легко определите, совпадает ли он с подграфом из базы, и конечно продумайте как будете разруливать ситуации, когда точек в базе нет.

Не пытайтесь сделать что то мегаоптимальное сразу, это как выше сказали сложно... но можно подойти к вопросу творчески.

Во первых, храните не сообщения пользователей, а чаты, соответственно и шардинг делайте не по пользователям а по чатам. Тогда сообщения одного пользователя будут размазаны сразу по нескольким серверам но сгруппированы по чатам, как раз чтобы сам чат (с любым количеством пользователей, обычно их количество в чате значительно меньше общего количества пользователей проекта) задействовал только один сервер.

Во вторых, разделяйте оперативную и архивную информацию, для чатов это окно сообщений, видимое пользователям по умолчанию при открытии чата (не все же вы загружаете, всегда это происходит порциями), но не делайте правило переноса между этими группами как аксиому, пусть это будет некий системный процесс, который на основе нагрузки и статистики перемещает данные. Если ограничить пользователей по редактированию и удалению старых сообщений, то возможно использовать разные подходы по хранению оперативной и архивной информации, разные технологии индексации или даже самого хранения, в т.ч. оптимизации по буферизации... грубо говоря хранить readonly архивные данные можно где угодно, ведь для доступа к ним не требуется синхронизация и блокировки.

Не обязательно городить прослойку, дающую информацию по тому где что хранится, например место размещения архивных сообщений чата можно хранить там же где оперативная, ее все равно будут запрашивать , мало того нужно везде стараться делать так, чтобы сразу было ясно, на каком сервере хранятся данные, самый простой способ это хеш от идентификатора (простой остаток от деления на количество серверов более чем подходит).

Оперативные данные будут генирировать самую высокую нагрузку, всеми силами делайте так, чтобы эти данные были как можно дольше в оперативной памяти, т.е. сервера, обрабатывающие их должны быть настроены отлично от архивных.

p.s. массовые рассылки от проекта или партнеров (не кривитесь, все это хотят делать) не делайте обычными чатами, а то будут получаться странные выверты с мегадублированием информации и огромной нагрузкой в момент их проведения, пусть это будет отдельная сущность (остальное разрулите в интерфейсе). С другой стороны, если вам нужна обратная связь, в тот момент, когда пользователь вдруг решает ответить в такой недочат - конвертировать его в обычный.

Каждый час - свой атрибут в таблице (т.е. 24-атрибута, а их значения в таблице - цены), ведь в запросах эти данные будут подставляться конкретным значением, а значит это можно делать генерируя сам запрос (это не повлияет на скорость его исполнения), зато не будет перегруженной структуры, лишних индексов и оверхеда на работу с ними = плюс к скорости и простоте разработки.

php-cli
Это отдельный, хоть и виртуальный пакет в операционных системах, имеет свой каталог с настройками расширений в /etc/, запускается приложение как и все скриптовые php имя файла плюс можно добавить первой строчкой по стандарту #!/usr/bin/php и поставить бит x на файл, тогда он будет запускаться как обычное приложение.

cron - это не порядок, он подходит наоборот для периодических задач, которые отработали и завершили работу.

По правилам, если вам нужен демон, который постоянно будет работать, то для этого необходимо создать для него файл службы, читать документацию по операционной системе или в /etc/init.d этот список все операционки тянут для совместимости до сих пор (там простой bash который запускает и останавливает вашу программу по команде start|stop).

Будьте внимательны, если вы об этом не позаботитесь сами, ваш скрипт самостоятельно никто не перезапустит, если он вылетит по ошибке.

У вас проблема не с большими числами, bigint их решит, а большой объем данных, миллионы и миллиарды записей положат вашу базу и создадут охренительные проблемы в будущем
Поэтому сразу закладывайте партиционирование таблиц по интервалам времени, причем не обязательно средствами базы данных, достаточно самим создавать новую таблицу для каждого следующего временного интервала (недели, месяцы - зависит от вашей нагрузки).

почему вы хотите хранить простые линейные логи в БД?
что еще вам нужно делать с данными кроме фильтрации и суммы?

если всетаки храните в базе, то не создавайте индексов на такие поля как объем трафика и url, это наиглупейшая ошибка, во время записи в таблицу заранее считайте необходимые параметры, выделяйте важные данные из url, вычисляйте домен, ip (сегодня он один, завтра другой), собирайте суммы по трафику в отдельной табличке, если нагрузка позволяет, можете по ip считать (или по зонам), в итоге вы будете работать не с сырыми данными, а уже агрегированными и посчитанными, их на порядок меньше и они удобнее для использования.

Ваша задача очень эффективно решается обычным файлом на диске (открытый с запретом lazy writes, либо принудительным flush после каждой записи). Если инструментарий (язык программирования и библиотеки) позволяет - откройте файл маппингом на память.
Индекс для таких запросов не нужен, ведь ячейки можно индексировать примитивно - (x+maxx*y). запрос 100x100 блока превращается в 100 быстрых чтений по 100байт. Если это оправдано, можно хранить не ячейки а блоки 100x100, тогда при чтении будет читаться в 4 раза больше данных но четырьмя чтениями. Но если блок данных экрана влезает в буфер опережающего чтения операционной системы (драйвере файловой системы) то такой метод хранения будет неактуален.

Любой другой метод будет медленный либо по записи (например хранить в базе не ячейки а блоки 100x100, соответственно при запросе блока будут считываться четыре соседних), либо медленный по чтению (хранить по одной записи на ячейку) и не эффективный по месту на диске.

Если у тебя varchar или text то при сравнениях регистр будет игнорироваться, так что по уму достаточно при выводе везде добавить uppercase.
p.s. а тригером на изменение, некрасиво но действенно.

Самое простое, что можно попробовать, сделайте 2 базы данных и настройте репликацию master->slave, из слейва читать, в мастер писать.
Даже в пределах одного сервера это может дать неплохой прирост (правда в результате нагрузка на сам сервер немного повысится), дальше можно крутить индексы, так же можно делать разные индексы на разных серверах master/slave. Главное постараться разделить физически хранилища для этих баз.

Можно периодически приостанавливать репликацию, увеличивая интервал между актуальными данные в мастере и анализируемыми данными в слейве.

Можно поднять слейв на ненадежном хранилище (tmpfs или включить принудительное кеширование записи, например в опциях монтирования ext3/ext4 — data=writeback), это увеличит скорость копирования данных в слейв.

А что именно тормозит при выгрузке списка с дубликатами постов? Не устраивает, с какой скоростью обрабатывает distinct, делайте дедубликацию самостоятельно, а чтобы не выгружать сами статьи, сначала получите список id а затем на их основе выгрузите нужные записи из posts
И делать это можно прямо на стороне сервера, складывая id во временную таблицу (in memory)
p.s. кстати, если количество статей за запрос сравнительно небольшое — сотни, вы можете делать это запросом select * from posts where id in (....)

Можно я присоединюсь к вопросу, расширив его до:

Имеется очень большое количество строк (в общем случае, с бинарными данными, конечно было бы лучше). Эти строки очень похожи! Размер строк варьируется в пределах от считанных байт до нескольких десятков килобайт.
лучшее что можно сказать про эти строки, — грубо говоря, это различные сообщения по некоторому количеству шаблонов (их количество тоже заранее неизвестное, но тоже большое, примерно количество сравнимое с log(n)). Нет возможности заранее получить эти шаблоны (источник данных независим), мало того, во времени шаблоны меняются, т.е. могут появляться новые и исчезать старые.

Задача, с некоторым приближением (речь не идет о максимальной эффективности), обнаруживать похожие сообщения, или в терминах описанной выше информации об этих строках — выявить шаблон для каждого сообщения. Уровень эффективности можно определить по размеру патча какого-либо diff-алгоритма (тот же bzdiff).

Если задачу решать нахрапом, то это для каждого следующего сообщения производить сравнение с каждым предыдущим (можно взять некоторое окно во времени), вычислять расстояние (размер diff патча) и брать наименьшее.

Дальше можно оптимизировать на основе предположения что если сообщение A находится на расстоянии от B и если C находится на примерно таком же раасстоянии от B, то расстояние между A и C будет примерно таким же, а значит если хранить матрицу расстояний между сообщениями (не для всех а только проверяемых) то расстояние для не проверенных пар можно вычислять из соседних 'соседних'. Можно даже пройтись по архиву и выявить коэффицент/погрешность, которая накапливается если использовать это вышеописанное предположение (A->B)&(C->B) = (A->C) многократно для A->D, A->E на основе таких же вычисленных B->D и B->E или даже D->E… в общем чтобы вместо трудоемкости N*M получить хотя бы N*log(M) где N — количество сообщений, M — размер окна, количество последних сравниваемых собщений (в этом случае их можно уже считать шаблонами).

Помимо партиционирования, я могу посоветовать из разряда 'вредных советов' (но лично так делал для более простых форматов хранения таблиц — myisam, с ними проще восстанавливать базу, вот с innodb я не очень в курсе, как легко будет поднять базу если индексный файл будет потерян)…

Разделите данные и индексы по разным файлам (а точнее физическим разделам, если конечно ваш тарифный план хостинга позволяет это). Индексы обычно занимают меньше места но основные тормоза при записи создают именно они (так как в них происходит перезапись порядка log(n) объемом данных (конечно все сложнее, много читается как из таблицы с данными так и из таблицы с индексами) и эта запись сервером баз данных делается в синхронном режиме.

Само по себе размещение индексов таблиц баз данных на более быстром (те же ssd) носителе заметно ускоряют работу, но я так понимаю это не ваш случай и предлагаю схитрить за счет понижения надежности, отключить синхронизацию записи данных для соответствующих файлов. Сделать это можно, разместив эти файлы на рам-диске (tmpfs) либо отформатировав их раздел под ext4 и примонтировать с опциями -o noatime,async,data=writeback.

Но прежде чем радоваться заметному повышению работу базы данных, рекомендую сначала протестировать и подготовить скрипты поднятия базы данных после нештатного выключения сервера (данные в tmpfs теряются безвозвратно, так же как некоторые данные для разделов, смонтированных с data=writeback). Так как на ненадежном хранилище размещаются не данные а индексы, то их потеря не критична (вопрос только во времени, требуемом для их полной перестройки).

Консоль в windows с кодировкой cp866, поэтому и 'кракозяблы'… что в test.txt?
mysql .... -e "select * from table" > test.txt

Если не перелопачивать базу данных, простенько, для галочки… то вместо текста в поле храните сериализованный массив {ru:'Бла, бла, бла',en:'Blah, Blah, Blah',..}…

Но в общем случае это не решит проблем, которых на самом деле гораздо больше появляется при переходе от одноязычного сайта к мультиязычному. Они все специфичные, вида что делать с данными, смысл которых 'перевод'… нужна ли новая сущность на них со ссылкой на оригинал или достаточно связи 1к1 и т.п.

Поиграйся так, не идеальное решение но иногда спасает:

SET <hh user=rank>=0;
SELECT <hh user=rank>:=<hh user=rank>+1 AS rank, id FROM menu;

Если я не ошибаюсь, практически любые DDL операции с myisam у mysql — это почти полное копирование, т.е. создается временная таблица, туда переливаются данные, после этого удаляются старые и переименовываются созданные.
Значит проблем не должно быть.

p.s. бакап/восстановление myisam таблиц возможен просто в виде файлов самих таблиц (скопируй всю папку /var/lib/mysql/база_данных )

Меняется ли sum(field2) для каждого field и как часто? Критично ли скорость его записи?
Если быть более точным, изменяется ли поле field2? или только добавляются и удаляются новые записи?

Я к тому что такие задачи решаются гораздо проще просто доп-таблицей (field, sum_cache) и обновлением на основе триггеров или самостоятельно… кстати на сколько я знаю есть БД поддерживающие кеш-индексы на основе выражений (фактически они и создают поле и наполняют его триггерами)

Иногда бывает эффективнее заранее пронумеровать записи пользователей в отдельном поле (однократно старые данные и затем при добавлении и удалении записей заново перенумеровывать), тогда запрос станет очень простым:

select * from статьи t where t.номер<=:limit

Какие сложные запросы и почти наверняка засовываются в главные страницы (т.е. будут всегда запрашиваться), неужели сложно добавить поле boolean need_translate, и на время, отсутствия перевода, вставить английский во все языки.

p.s. лучше делать так: таблица towns {id, ru,en,fr,...} т.е. по полю на язык, соответственно запросы будут проще и шустрее.