Блог n0wheremany

Привет всем.
Недавно обратил внимание на некоторые особенности конструирования запросов MySQL и способы оптимизации и на примере ДЛЕ решил их проанализировать и подправить на уменьшение нагрузки. Т. к. самое узкое место в любой CMS это база.

1. Запрос вида SELECT SQL_CALC_FOUND_ROWS ..., SELECT FOUND_ROWS();

Пример запроса

SELECT SQL_CALC_FOUND_ROWS p.id FROM dle_post p WHERE p.id=1;
SELECT SQL_NO_CACHE FOUND_ROWS();

Аналог

SELECT p.id FROM dle_post p WHERE p.id=1;
SELECT count(*) FROM dle_post p WHERE p.id=1;

Данных запрос встречается и по сей день в новых версиях ДЛЕ.
Для чего он нужен? он подсчитывает количество строк без учета ограничения выборки LIMIT для формирования пагинации и единственное отличие от аналога - то, что там не прописывается ограничение WHERE и тем он удобнее.
По сравнению скорости выполнения можно почитать на хабре. Так же на просторах инета читал, что с использованием ORDER BY данных запрос тяжелее аналога.

Однако я считаю лучше использовать аналог и такую схему работы:
1. Получаем количество строк в запросе
2. Рассчитываем пагинацию, и если запрашиваемая страница больше всего страниц - отсылаем юзера на 404 или на последнюю страницу.
3. Получаем данные.

2. Запрос вида WHERE id in (SELECT) на примере Тэгов

Пример 1

SELECT news_id FROM dle_tags WHERE tag = '2011'; /*Запрос 1*/
SELECT p.id FROM dle_post p WHERE p.id in (Результаты запроса 1);

Пример 2

SELECT p.id FROM dle_post p WHERE p.id in (SELECT news_id FROM dle_tags WHERE tag = '2011');

Пример 3

SELECT p.id FROM dle_post p INNER JOIN dle_tags t on (t.news_id=p.id and t.tag = '2011');

В ДЛЕ используется 1 вариант выгрузки новостей по тэгам: Сначала ищутся ID новостей, потом просто по ним формируется список новостей. Запросы простые, но у данного способа есть узкое место - получение ID новостей.
Если новостей по выборке много, то это довольно тяжко перебрасывать мегабайты ненужных данных.
Как её избежать - логично воспользоваться примером 2. Однако, если воспользоваться EXPLAIN, то можно увидеть, что выборка идёт не по индексам и строенный оптимизатор запросов криво формирует запрос (Идет поиск всех новостей, потом формируется фильтрация по ID). После 6 версии это поправили.
Как можно избежать использования 2х запросов и такой тяжёлой операции из 2 примера - Использовать 3 ).
В 3 примере мы джойним с отсечение ненужных новостей сразу, пj EXPLAIN это сразу видно. Так что учитывайте этот момент при конструировании запросов. Кстати в MSSQL разницы нет - использовать in SELECT или INNER JOIN.

3. Запрос отображения новостей по категориям (если категорий у новости может быть несколько) REGEXP '[[::]]'

Пример 1

SELECT p.id FROM dle_post p WHERE p.category REGEXP '[[:<:]](1)[[:>:]]'

Пример 2

SELECT p.id FROM dle_post p WHERE FIND_IN_SET('1',p.category)>0

На текущий момент в ДЛЕ используется поиск новостей в категории с помощью регулярки, если новость имеет несколько категорий. Данный запрос очень толстый, т. к. не используются индексы, и что бы получить новости MySQL читает все новости, потом делает выборку по регулярке.
Каким образом можно оптимизировать запрос?
Если не включать опцию "Выводить новости опубликованные в субкатегориях", то вам поможет запрос из примера 2. При тестировании у меня этот запрос оказался легче на 30-60%. Если вас данная оптимизация не устраивает, то необходимо создать таблицу связей накладная-категория, как сделано для тэгов. В таком случае запрос будет выполняться мгновенно.

И последнее на закуску
4. Поиск по доп полям

В последней версии ДЛЕ есть таблица связей новость-доп поле. Формат запросов соответствует формату поиска по тэгам. Для старых версий умельцы составили такой вот запрос

SELECT p.id FROM dle_post p WHERE SUBSTRING_INDEX( SUBSTRING_INDEX( p.xfields, '{ИМЯ ДОП ПОЛЯ}|', -1 ) ,  '||', 1 )='{ЗНАЧЕНИЕ ДОП ПОЛЯ}'

Можно воспользоваться таким запросом, у меня при тесте он получился чуть быстрее 5-10%

SELECT p.id FROM dle_post p WHERE CONCAT('||',p.xfields,'||') LIKE '%||{ИМЯ ДОП ПОЛЯ}|{ЗНАЧЕНИЕ ДОП ПОЛЯ}||%';

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

SELECT p.id FROM dle_post p WHERE CONCAT('||',p.xfields,'||') regexp '\\|\\|{ИМЯ ДОП ПОЛЯ}\\|[^\\|\\|]*{ЗНАЧЕНИЕ ДОП ПОЛЯ}[^\\|\\|]*\\|\\|'

По тяжеловесности такой запрос вне конкуренции )))

Подведу Итог - в ДЛЕ есть ещё куда стремиться по оптимизации запросов. Так что ждём новые версии, в которых реализованы не только стыбренные идеи модулей, но качественное совершенствование кода и запросов.