Скачать Статья Оптимизирем запросы поиска по DLE сайту

Проделав описанные ранее действия по оптимизации работы сервера и движка DataLife Engine, мой сайт проработал еще какое-то время. База выросла до 160 Мб (28000 публикаций). В файл, в который пишутся запросы к БД выполняющиеся более 10 секунд, появились записи следующего вида:

SELECT id, autor, dle_post.date AS newsdate, dle_post.date AS date, short_story AS story, dle_post.xfields AS xfields, title, descr, keywords, category, alt_name, comm_num AS comm_in_news, allow_comm, rating, news_read, flag, editdate, editor, reason, view_edit, tags, ” AS output_comms FROM dle_post WHERE dle_post.approve AND category IN (’2′, ‘30′, ‘12′, ‘28′, ‘29′, ‘13′, ‘14′, ‘15′, ‘5′, ‘16′, ‘17′, ‘18′, ‘19′, ‘20′, ‘21′, ‘22′, ‘23′, ‘24′, ‘25′, ‘26′, ‘27′, ‘9′) AND (short_story LIKE ‘%малолетка%’ OR full_story LIKE ‘%малолетка%’ OR dle_post.xfields LIKE ‘%малолетка%’ OR title LIKE ‘%малолетка%’) ORDER BY date desc LIMIT 0,20;

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

Посмотрим, какие же запросы использует DataLife Engine для выполнения поиска по сайту:

SELECT COUNT … FROM table WHERE name LIKE ‘%string%’ – для получения количества записей удовлетворяющих поиску (для страничной навигации)
SELECT … FROM table WHERE name LIKE ‘%string%’ ORDER BY name LIMIT X,Y – сам запрос поиска на интересующей нас странице.

Таким образом при поиске по сайту DataLife Engine использует два запроса.

Начиная с MySQL 4.0, мы имеем возможность используя постраничную навигацию использовать не два запроса, а воспользоваться одним:

SELECT SQL_CALC_FOUND_ROWS …

Для получения общего количества совпадений используется FOUND_ROWS() который не занимает на выполнение времени и выполняется мгновенно.

Плюс ко всему, практически было установлено, что запрос

SELECT SQL_CALC_FOUND_ROWS … FROM table WHERE name LIKE ‘%string%’ ORDER BY name LIMIT X,Y

выполняется быстрей чем просто

SELECT … FROM table WHERE name LIKE ‘%string%’ ORDER BY name LIMIT X,Y

Кстати в DataLife Engine при выводе кратких новостей используется именно SELECT SQL_CALC_FOUND_ROWS. Почему разработчики не применили эту конструкцию в поиске по сайту, а воспользовались запросами, которые использовались в старых версиях MySQL непонятно.

Итак, было принято решение о изменении поисковых запросов на SQL_CALC_FOUND_ROWS конструкцию. Для этого отредактируем файл /engine/modules/search.php.

Найдем

$posts_fields = “SELECT id, autor, ” . PREFIX . “_post.date AS newsdate, ” . PREFIX . “_post.date AS date, short_story AS story, ” . PREFIX . “_post.xfields AS xfields, title, descr, keywords, category, alt_name, comm_num AS comm_in_news, allow_comm, rating, news_read, flag, ” AS output_comms”;

и заменим на

$posts_fields = “SELECT SQL_CALC_FOUND_ROWS id, autor, ” . PREFIX . “_post.date AS newsdate, ” . PREFIX . “_post.date AS date, short_story AS story, ” . PREFIX . “_post.xfields AS xfields, title, descr, keywords, category, alt_name, comm_num AS comm_in_news, allow_comm, rating, news_read, flag, editdate, editor, reason, view_edit, tags, ” AS output_comms”;

Найдем

$posts_count = “SELECT COUNT(*) AS count $posts_from $where”;

и заменим на

$posts_count = “SELECT FOUND_ROWS() as count”;

найдем

$comms_fields = “SELECT  ” . PREFIX . “_comments.id AS coms_id, post_id AS id, ” . PREFIX . “_comments.date, ” . PREFIX . “_comments.autor AS autor, ” . PREFIX . “_comments.email AS gast_email, ” . PREFIX . “_comments.text AS story, ip, is_register, name, ” . USERPREFIX . “_users.email, news_num, ” . USERPREFIX . “_users.comm_num, reg_date, banned, signature, foto, fullname, land, icq, ” . PREFIX . “_post.date AS newsdate, ” . PREFIX . “_post.title, ” . PREFIX . “_post.category, ” . PREFIX . “_post.alt_name, ” . PREFIX . “_post.comm_num AS comm_in_news, ” . PREFIX . “_post.allow_comm, ” . PREFIX . “_post.rating, ” . PREFIX . “_post.rating, ‘1′ AS output_comms, ” . PREFIX . “_post.flag”;

и заменим на

$comms_fields = “SELECT SQL_CALC_FOUND_ROWS ” . PREFIX . “_comments.id AS coms_id, post_id AS id, ” . PREFIX . “_comments.date, ” . PREFIX . “_comments.autor AS autor, ” . PREFIX . “_comments.email AS gast_email, ” . PREFIX . “_comments.text AS story, ip, is_register, name, ” . USERPREFIX . “_users.email, news_num, ” . USERPREFIX . “_users.comm_num, reg_date, banned, signature, foto, fullname, land, icq, ” . PREFIX . “_post.date AS newsdate, ” . PREFIX . “_post.title, ” . PREFIX . “_post.category, ” . PREFIX . “_post.alt_name, ” . PREFIX . “_post.comm_num AS comm_in_news, ” . PREFIX . “_post.allow_comm, ” . PREFIX . “_post.rating, ” . PREFIX . “_post.rating, ‘1′ AS output_comms, ” . PREFIX . “_post.flag”;

найдем

$comms_count = “SELECT COUNT(*) AS count $comms_from $where”;

заменим на

$comms_count = “SELECT FOUND_ROWS() as count”;

найдем

$sql_fields = “SELECT id, name AS static_name, descr AS title, template AS story, allow_template, grouplevel”;

зменим на

$sql_fields = “SELECT SQL_CALC_FOUND_ROWS id, name AS static_name, descr AS title, template AS story, allow_template, grouplevel”;

найдем

$sql_count = “SELECT COUNT(*) AS count $sql_from $where”;

заменим на

$sql_count = “SELECT FOUND_ROWS() as count”;

найдем

// —— Запрос к базе

                         $result_count = $db->super_query($sql_count, true);

                        $count_result = $result_count[0]['count'] + $result_count[1]['count'];

                         $min_search = (@ceil($count_result / $config_search_numbers) – 1) * $config_search_numbers;

                         if ($min_search < 0) $min_search = 0;

                        if ($search_start > $min_search) {

                                   $search_start = $min_search;

                        }

                        $from_num = $search_start+1;

                         $sql_request = “$sql_find ORDER BY $order_by LIMIT $search_start,$config_search_numbers”;

                         $sql_result = $db->query($sql_request);

                        $found_result = $db->num_rows($sql_result);

заменим на

// —— Запрос к базе           

                                   $sql_request = “$sql_find ORDER BY $order_by LIMIT  $search_start,$config_search_numbers”;                                  

                                   $sql_result = $db->query( $sql_request );

$result_count = $db->super_query( $sql_count );

                                   $count_result = $result_count['count'];

                                  $min_search = (@ceil( $count_result / $config_search_numbers ) – 1) * $config_search_numbers;  

                                   if( $min_search < 0 ) $min_search = 0;

                                   if( $search_start > $min_search ) {

                                               $search_start = $min_search;

                                   }

                                   $from_num = $search_start + 1;

                                   $found_result = $db->num_rows( $sql_result );

После проделанной работы, запросы поиска по сайту стали выполняться в 1,5 – 2 раза быстрее, что привело к снижению нагрузки на сервер и сокращению времени на генерацию страницы для посетителя сайта выполняющего поиск.