ТОП 10 популярных тем на форуме

[Alexander II]

Задача: повесить на сайт список самых популярных тем форума на текущий момент.

Решение: выбрать темы которые удовлетворяют следующим условиям:

• Тема одобрена
• Тема открыта
• В список не попадают темы закрытых разделов и корзины
• В список попадают темы, в которых последний ответ был не позднее суток назад
• Темы сортируются обратно по количеству отобранных ответов

Информация должна обновляться не реже одного раза в час (крон решает).

Учитывая все эти условия, был сделан следующий SQL селект:

Код Выделить Развернуть

SELECT
     smf_messages.id_topic,
     count(smf_messages.id_topic) AS cnt
FROM
     smf_messages,
     smf_topics
WHERE
     smf_topics.id_topic = smf_messages.id_topic AND
     smf_topics.approved = 1 AND
     smf_topics.locked = 0 AND
     smf_topics.id_board <> 95 AND
     smf_topics.id_board <> 97 AND
     smf_topics.id_board <> 80 AND
     smf_topics.id_board <> 30 AND
     smf_topics.id_board <> 101 AND
     smf_messages.poster_time > UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
GROUP BY
     smf_topics.id_topic
ORDER BY
     cnt DESC LIMIT 10

Однако, данный селект напрочь вешает систему. Загрузка процессора — почти 100% и надолго.

Вопрос: можно ли как нибудь оптимизировать данный селект? Если можно, то как?

PS
SFM 2

[digger®]

Этот запрос делает выборку ВСЕХ сообщений форума за последний день , а потом еще и количество сообщений для каждой темы суммирует. Конечно все поляжет от такого. Откройте для себя Explain. И почему вы количество сообщений из smf_topics не берете?

Sent from my CUBE U9GT 2 using Tapatalk 2

[Alexander II]

Откройте для себя Explain

Да, интересная функция, спасибо.

Sent from my CUBE U9GT 2 using Tapatalk 2

Что это? 

[Alexander II]

И всё таки, как оптимизировать запрос?

[Alexander II]

И почему вы количество сообщений из smf_topics не берете?

Хм... Не знал. Попробую.

[digger®]

И всё таки, как оптимизировать запрос?

Группировку уберите, а количество сообщений берите из smf_topics

Sent from my CUBE U9GT 2 using Tapatalk 2

[Alexander II]

Группировку уберите, а количество сообщений берите из smf_topics

Ерунда получается. Я выбираю не общее количество ответов в теме, а количество, написанное за последние сутки.

[digger®]

Ерунда получается. Я выбираю не общее количество ответов в теме, а количество, написанное за последние сутки.

Да, не полностью вник в суть вопроса.

[maestrosite.ru]

Во-первых бьёт по глазам это:

Код Выделить Развернуть


     smf_topics.id_board <> 95 AND
     smf_topics.id_board <> 97 AND
     smf_topics.id_board <> 80 AND
     smf_topics.id_board <> 30 AND
     smf_topics.id_board <> 101 AND

попробуйте NOT IN (95, 97, и тд)

Но главное, разбейте на два запроса:
1. выборка сообщений по времени (одна таблица, группировка) = получаете ид тем и количество сообщений
2. фильтрация тем (сколько надо таблиц, без группировки) = в имеющийся запрос добавьте полученные в первом запросе ид тем.

На втором шаге если нет названий тем или информации по авторам сообщений, то пересечения соответствующих таблиц.

[Alexander II]

Во-первых бьёт по глазам это:попробуйте NOT IN (95, 97, и тд)

Но главное, разбейте на два запроса:
1. выборка сообщений по времени (одна таблица, группировка) = получаете ид тем и количество сообщений
2. фильтрация тем (сколько надо таблиц, без группировки) = в имеющийся запрос добавьте полученные в первом запросе ид тем.

На втором шаге если нет названий тем или информации по авторам сообщений, то пересечения соответствующих таблиц.

Спасибо, попробуем.

[Alexander II]

Всё получилось, всем спасибо.

Скрипт стал отрабатываться всего несколько секунд.

Отдельное спасибо digger'у за попытку помочь и EXPLAIN и maestrosite.ru за NOT IN и дельный совет!!!

PS
tuning-primer.sh для MySQL > 5.2 никто не видел? =)