Большая БД и Limit X,Y

классический вариант

ids = SELECT id FROM table [WHERE where] ORDER BY order LIMIT 100500, 10

SELECT * FROM table WHERE id IN (ids)

Я не знаю значения where

Ужасный и избыточный вариант, с каких пор он стал классическим?

Первый запрос точно так же заставляет пройтись по всей базе как и заставил бы пройтись запрос со *.

Если у Вас "тупой" запрос, без сложных фильтров, то можно тупо сделать дубль таблицы - вынести id, name (ИД и сортировочное поле) в отдельную таблицу, в которой не будет лишних полей и скорость будет очень прикольная.

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

Можно как вариант, можно записывать хотя бы допустим последний ИД для текущей страницы временно (допустим 43242 оказался последним в выборке на 3 странице), и дальнейшую выборку делать уже от него (обычно через 100-500 страниц не листают все же).

p.s.: на хабре до фига статей на эту тему, рекомендуем полистать.

Запрос с фильтрами, как то поиск по полям.

Пользователи да, не листают далеко, но есть поисковики, которые рано или поздно пройдут все страницы и в выдачу попадут оные - пользователь с выдачи пойдет на страницу 100500 и нагрузит сервер, а если пользователей тонны, то нагрузка будет постоянная.

Была идея кешировать все страницы, ну или по ID и сортировочному полю, но контент меняется, меняются и страницы, т.е. кешировать не получается.

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

Скорость запроса limit 100500,10 зависит в первую очередь от размера таблицы. Поисковики обычно ходят по страницам вида "год такой-то" и так далее. Мы для таких целей иногда делали специальную кэширующую таблицу, в которую сводили только те вещи, по которым идет "штатный" фильтр.

То есть если по стандарту это id, title, content, year, author, genre где поля типа int, varchar, longtext, int, int, int а выборка происходит только по year и genre, то имеет великий смысл сделать таблицу вида id, year, genre - по которой запросы limit 100500,10 будут очень шустрыми (в меру ее мелкого размера). То есть это по сути эдакий мутировавший аналог индексов, но т.к. индексы лимитам не помощники, а уменьшение размера таблицы помощник - это решает. Вместо 2Гб текста у Вас внезапно начинает перелопачиваться 20мб целых чисел, а если их пихнуть еще в мемори таблицу, то получается самолет при этом не теряющий в функциональности.

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

Дополнительная таблица, плюс правильно выбранный движок MYSQLя.

Точно не помню, то ли MyIsam, то ли InnoDB. Пошарьте в интернете, там есть одназначно. Просто какие-то движки заточены под ИНСЕРТ, а другие летают на СЕЛЕКТе.

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

В общем сделал кеш (отдельную таблицу) под один запрос - время генерации упало в 10 раз, с 0.3 сек до 0.03.

Осталось сделать под остальные 10 запросов.

ТС, время начала топика - 22:05, решение проблемы - 23:23. Всего лишь час 18 и все.

Не сочтите за флуд, но просто очень приятно быть частью серча:)

ЗЫ поздравляю всех с наступающим прадздником!!!

humbert, возможно, имеет смысл (если вообще возможно-уместно) попробовать сфинкс.. Помимо поиска по словам с учётом семантики, можно указать нужные индексы.. не особо замудрёный SQL понимает - из PHP работать несложно. Переиндексировать по мере необходимости (насколько критичен неактуальный результат?)

Ну и статья (уверен, что всё читано.. т.к. написана давненько) http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ , в комментариях к которой автор упоминает SphinxSearch

А вообще вариант удачный.. "Кэширующая" (несколько) а-ля индексная таблица в памяти + (если есть смысл) триггер на "основную" таблицу для обновления кэширующих.. Или по таймеру - смотреть, что "приятнее".

Аха, есть такое. Поэтому я уже поднимал на этом форуме темку. У меня 2 ГБ бд уже. И этот лимит такой тормоз. Поэтому для себя решил вот таким кодом:

$pages=intval($_GET['p']); # Номер страницы



$nums=$pages-1; if ($nums<0) $nums=0;



$result=mysql_query("SELECT id FROM `cms_freepages` WHERE cat=".$category[$_GET['url']]['id']."");

while ($rowclubs = @mysql_fetch_array($result)) 

{

$arrayid[]=$rowclubs['id'];

}

mysql_free_result($result);

$cnt=sizeof($arrayid);

arsort($arrayid);

$newmass=array_chunk($arrayid,50); # 50 результатов на странице

if (!sizeof($newmass[$nums])) {header("Location: /404.php");exit;}

$sql = mysql_query('SELECT * FROM `cms_freepages` WHERE id in ('.implode(',',$newmass[$nums]).') ORDER BY ID DESC');

Этот код меньше всего нагружает mysql и работает с первичными индексами.

p.s. не надо мне лекций, что можно сделать вложенные запросы и всё будет тоже самое, только без учасние php. А вот и хрен вам :D именно то что мы всё бросаем на php и даёт колоссальный выигрыш в больших таблицах и при больших нагрузках.