Оптимизирем запросы поиска по DLE сайту | Вебмастер DataLife Engine

Проделав описанные ранее действия по оптимизации работы сервера и движка DLE, мой сайт проработал еще какое-то время. База выросла до 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;

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

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

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

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

 Начиная с 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

 Кстати в DLE при выводе кратких новостей используется именно 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 раза быстрее, что привело к снижению нагрузки на сервер и сокращению времени на генерацию страницы для посетителя сайта выполняющего поиск.

1 Комментарий
 

Один комментарий на Оптимизирем запросы поиска по DLE сайту

  1. Денис - 04.04.2011 - 12:46

    вот у меня такая проблема с поиском есть 2 одинокавыеновости и как мне сделать чтобы выводилась только 1 в кратком поиске, еслизнаешь как это сделать скажи плиз

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *