Проделав описанные ранее действия по оптимизации работы сервера и движка 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 раза быстрее, что привело к снижению нагрузки на сервер и сокращению времени на генерацию страницы для посетителя сайта выполняющего поиск.
Всего: 637 
Новых за месяц: 0 
Администраторов: 1 
Модераторов: 1
Проверенных: 11 
Обычных юзеров: 620 
Девушек: 17