Многотабличные запросы

[Serg@NT]

Тестировал на своём сайте время отображения страницы (простенький код в PHP, т.е. за сколько сервер генерирует всю страницу).
Есть 2 таблички MySQL (MyISAM) - статьи (примерно 2000 записей) и комментарии (примерно 1000 записей). На странице новостей показывается примерно 25 новостей: название, описание из статей и количество комментариев к каждой.
Когда-то давно, когда писал эту страницу, ещё не имея достаточного опыта, сделал это примитивно: выборка последних 25 статей и для каждой ещё по запросу - выборка количества комментариев ("SELECT COUNT(*) FROM comments ...") - в цикле while($articles = mysql_fetch_array($art)). Итого получается 26 однотабличных запросов к базе.
Сегодня решил "оптимизировать", сделав запрос к обоим таблицам сразу, в виде "SELECT a.*, COUNT(c.id) FROM articles a, comments с ...". То есть, чтобы и информация о статье, и о количестве комментариев выбиралась в одном запросе.
Однако, заметил, что из-за этого время генерации страницы резко возрасло. Было меньше секуны (0,3 - 0,5 сек), стало - 4 с лишним. Пришлось вернуться к предыдущей схеме. То же самое пробовал для архива новостей, где выдаётся по 100 новостей на страницу - та же фигня, даже время то же.
Я понимаю, что запросы к нескольким таблицам выполняются дольше, чем к одной, но не настолько же! Объясните, кто может.

 

[Creator]

Итого получается 26 однотабличных запросов к базе.

Такие легкие запросы очень хорошо кэшируются БДой, что в итоге дает неплохой профит.

Но для того, что бы разобраться нужно научиться профилировать запросы.
Для этого нужно использовать explain select ...
Агрегирование данных само по себе дорого по ресурсам, особенно когда объединяются несколько таблиц, к тому же мне кажется, что индексы вами выбраны не оптимальные...
Приведите вывод explain для запроса вывода всех статей, подсчета ком. 1 статьи и запроса с агрегированием данных.

Рекомендую не использовать агрегацию, а хранить результаты (число комментов) в отдельном поле таблицы новостей/статей. В свое время я тоже допустил эту ошибку=)

А еще лучше это кэширование всего и вся) но это уже другая история.

 

[trueW3C]

Так быстрей

SELECT COUNT(id) FROM comments

Комментарии каждый раз считать не надо, добавь в таблицу статей поле comments и при добавлении комментария делай запрос

UPDATE posts SET comments = comments + 1 WHERE id = 1

При удалении соответственно

UPDATE posts SET comments = comments - 1 WHERE id = 1

И того на странице выбора статьи будет 2 запроса...

SELECT count(id) FROM posts

SELECT * FROM posts LIMIT 0, 10

Первый запрос можно кешировать, записываешь общее число записей в файл и обновляешь его когда добавлены или удалены статьи

$cache = 'countPost.php';

if(is_file($cache))
    $count = file_get_contents($cache);
else
{
    $result = mysql_query('SELECT count(id) FROM posts');
    $rows = mysql_fetch_assoc($result);
    $count = $rows['count(id)'];
    
    file_put_contents($cache, $count);
}

Когда добавляешь статьи, просто удаляй файл...

unlink($cache);

P.S. Функция file_put_contents() есть только в PHP 5 и выше

 

[DnAp]

Потомучто выбирается сначала кол-во записей умноженное на кол-во комментов, после этого делается группировка по статьям и считается count.
А когда в цикле они получаются, скорее всего участвуют индексы что дает большой прирост.
Вот кстати выше, trueW3C, правильно сказал по поводу записать кол-во комментов в табличку записей. А на счет кэширования в файлике я не совсем согласен.

 

[trueW3C]

Почему? Я правда никогда его не делаю, но это способ кеширования...если у вас больше 10 000 записей...

Хотя да, MySQL сама может вроде кешировать такие запросы..

 

[DnAp]

Просто с файлами если нагрузка низкая ничего выиграть не получиться. А если высокая то есть вероятность словить 2 параллельных изменения(web 1.0 уже ведь умер?). Ну и там уже стоит присматриваться к memcache, но это уже другая история.