Как хранить и выбирать данные для пейджинга?

Question

[Sergic]

В общем, точно сформулировать вопрос не могу, просто опишу ситуацию.

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

Все мы обычно начинаем из подсчета числа фильтрованных данных SELECT COUNT(*), потом уже через LIMIT и OFFSET делаем запрос на объекты. И беда в том, что, даже, если есть индексы, то запросы выполняться в 10-100 раз дольше чем обычные.

Тут возник вопрос как оптимизировать? Кешировать объекты куда-то в память? Что еще использовать и как использовать? Хочется решить проблему, но я просто не представляю, что делать.

К тому же, использовать memcache, не знаю, или целесообразно для хранения count т.к. есть множество фильтров, и данные постоянно обновляются и уследить за фильтрами очень сложно.
Если кто-то сталкивался с данной проблемой, подскажите, пожалуйста. Спасибо.
--
Если использовать, к примеру, PerconaDB или включить еще сохранение промежуточных данных на MongoDb?

Comments

[Denis Morozov]

я дал два общих совета в ответе

расскажите как выглядят таблицы из которых вы хотите быстро выбирать данные и по каким условиям(в смысле where) вы делаете выборку и тогда мы с вами сможем придумать что-то более конкретно для вашего случая

[Sergic]

Смысл такой, что даже
SELECT count(m_.id) AS sclr FROM message m_ WHERE m_.status = 1 AND m_.category_id = 1 ORDER BY m_.published DESC и с индексом
как по мне казалось много
часть базы:
В общем куда дальше двигаться не знаю, оставлять задержки в 200мс-1сек как-то стремно. По ходу нужны кеши или горизонтальное масштабирование базы данных :) ?

[Denis Morozov]

для начала попробуйте два таких индекса:
1. составной индекс по двум полям m_.status и m_.category_id
2. индекс по полю m_.published

этот такой запрос должен заработать быстрее

SELECT count(m_.id) AS sclr FROM message m_ WHERE m_.status = 1 AND m_.category_id = 1 ORDER BY m_.published DESC

сделайте эти два индекса и сообщите о результатах

и так как нужен педжинг, то надо добавить LIMIT n, m - вытянуть полученные данные и отобразить их тоже время нужно

[Sergic]

@morozovdenis Спасибо за помощь! Я решил сделать аудит всех композитных ключей, что есть в таблице, и многие проблемы обнаружил. Еще нашел статью doc.prototypes.ru/database/postgresql/indexes/comp... В общем, оптимизацию БД провел на ура. Удалил из запроса count(*) ... order by, он вообще в моем случае не нужен. Перебрал варианта деревья ключей и нашел оптимальные...

Answer 1

[Denis Morozov]

1. денормализуйте схему БД

2. дублируйте данные в таком виде что-бы было можно было эффективнее выбирать их

Comments

[Sergic]

@morozovdenis спасибо! Уточнил сообщение.

Answer 2

[Сергей Протько]

How to improve Mysql select count(*) performance

Comments

[Sergic]

Спасибо. Индексы все на месте, из статьи я понял что, по моим задержкам это норма)

Answer 3

[Петруша Укропов]

valera.ws/2007.08.29~sql_calc_found_rows