Форум MySQL

Из PHP-скрипта выполняется следующий запрос:

SELECT    hosts.id_host,   hosts.name,    hosts.description,   messages.id_mnemonic FROM    `messages` JOIN    `hosts`    ON     messages.id_host=hosts.id_host WHERE   messages.log_date>subtime(now(),'01:00:00') GROUP BY   hosts.id_host ORDER BY    hosts.severity, hosts.id_host

Типы полей:
hosts.id_host - integer;
hosts.name - text;
hosts.description - text;
messages.id_mnemonic - text;
messages.log_date - datetime;
hosts.severity - integer;
в таблице messages - 300000 записей,
hosts - 400 записей.
Запрос не выполняется до истечения таймаута (даже учитывая, что сообщений за последний час порядка 5000), но выполняется примерно за 6 секунд, если убрать группировку.
Подскажите, пожалуйста, можно ли каким-то образом оптимизировать данный запрос, не изменяя настроек PHP-сервера?

Неужели нет какого-то решения данного вопроса? Помогите, пожалуйста

А если так?

SELECT    hosts.id_host,   hosts.name,    hosts.description,   messages.id_mnemonic FROM    `messages`,   `hosts`  WHERE   messages.log_date>subtime(now(),'01:00:00') AND     messages.id_host=hosts.id_host GROUP BY   hosts.id_host ORDER BY    hosts.severity, hosts.id_host

И индексы проверь...

Создал индекс на messages.log_date - стало выполняться влет, спасибо. Теперь еще один вопрос возник:
Простой запрос, возвращающий записи из таблицы (800 000 строк) за последний час, выводит 1000 разных строк за доли секунды, а на вывод 55 повторяющихся записей требуется 15-20 секунд. Вот текст запроса:

SELECT     id_mnemonic FROM     `messages` WHERE     id_host =5     AND     syslog_date > SUBTIME( NOW( ) , '01:00:00' )

Все поля, получается, индексированные, в основном внешние ключи. Таблица - InnoDB

>Простой запрос, возвращающий записи из таблицы (800 000 строк) за последний час, выводит 1000 разных строк за доли секунды, а на вывод 55 повторяющихся записей требуется 15-20 секунд. Вот текст запроса:
>

>SELECT >    id_mnemonic >FROM >    `messages` >WHERE >    id_host =5 >    AND >    syslog_date > SUBTIME( NOW( ) , '01:00:00' ) >

это этот запрос 20 секунд выполняется?

Да, именно так. Похоже, чтобы ощутить, надо иметь таблицу с миллионом записей. Причем интересно, что запросы по некоторым хостам (их порядка 250) выполняются за доли секунды, а некоторые - как уже сказал, 15-20 секунд.
И еще, что странно и никак не изменяется, количество индексов по полю id_mnemonic всего 15-17, хотя значения этого поля принимают примерно 10000 значений. Вот такая вот незадача.

А какие запросы в принципе применяются к этой таблице и какие применяются наиболее часто?

индекс на id_mnemonic врядли будет играть роль.
Скорее на id_host или на syslog_date. Нужно смотреть план.

Таблица содержит записи логов с временем поступления внешними ключами, которые ссылаются на адреса устройств (hosts) и модулей (pp_card), описания элементов логов (mnemonics). Соответственно, наиболее частые запросы - выборка сообщений по хосту и степени важности (base_severity). Фактически приведенный мною запрос будет заменен таким:

SELECT    count(*)as cnt,    base_severity FROM    `mnemonics`    JOIN   `messages`   ON      messages.id_mnemonic=mnemonics.id_mnemonic WHERE    messages.id_host={Номер хоста}   AND   syslog_date>=(NOW()-INTERVAL 1 HOUR)   GROUP BY base_severity   ORDER by base_severity

Но,так как не выполняется простой запрос, пока нет смысла заменять его на более сложный. Со сложным ситуация абсолютно такая же, как и с простым запросом.

Также к этой базе данных применяется запрос, листинг которого в моем первом сообщении в этой теме
схема данных в прикрепленном файле

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

Тогда размер активной таблицы уменьшится, а скорость доступа возрастет.

А можно ли как-то объяснить ситуацию, когда в таблице `messages` в полях id_message и syslog_date столько же индексов, сколько строк, а в поле id_mnemonic - 15 при 9000 принимаемых значений?

может надо не объяснить, а починить?

REPAIR  messages

Таблица InnoDB, REPAIR не работает. CHECK TABLE говорит, что все ОК

а индекс построен на всей длине ключа? Или только на его части?

Index kind - index;
index type - BTREЕ.

Как можно посмотреть, на какой части ключа построен индекс?

Да прямо в структуре таблицы.

KEY `fullFieldIndex` (`field`),   KEY `first5charsIndex` (`field`(5)),

Построен по всему полю, тип данных - Integer

а с чего Вы решили, что их всего 15?

>а с чего Вы решили, что их всего 15?

Из PHPmyAdmin

А остальные обслуживающие запросы индексы не восстанавливают?
CHECK TABLE messages;
ANALYZE TABLE messages;
OPTIMIZE TABLE messages;

Нет, ни один из них

Нашел решение вопроса. Выглядит следующим образом:

SELECT *,count(*) AS cnt FROM   (SELECT base_severity,id_host   FROM messages JOIN mnemonics USING (id_mnemonic)   WHERE syslog_date>(NOW()-interval 1 hour) ) AS res WHERE id_host=1 GROUP BY base_severity

Запрос выполняется очень быстро. Судя по всему, проблемы с оптимизатором базы данных?

Полагаю, как только Вы напишете LEFT JOIN , перформанс упадет вниз.
У Вас, похоже, куча записей, в которых id_mnemonic либо не задано либо не найдено в таблице mnemonics.
Ну и левый GROUP BY тоже однозначность в ситуацию не вносит.

Но поле id_mnemonic в таблице `mnemonics` ключевое, и id_mnemonic в таблице `messages` является внешним ключом, соответственно, между всеми записями этих таблиц есть соответствие "1:N"

А GROUP BY?

Не понимаю, в чем суть вопроса.
Вы имеете в виду, что GROUP BY находится во внешнем запросе, а не в подзапросе?

Я имею в виду , что ни
SELECT base_severity,id_host FROM messages JOIN mnemonics USING (id_mnemonic)... GROUP BY base_severity
ни тем более SELECT * FROM table... GROUP BY base_severity
не являются запросами однозначной трактовки.

В приличных серверах такие запросы будут вызывать сообщения об ошибках:
Column 'messages.id_host' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
а не выдавать результат.

Спасибо за информацию. А где можно почитать о "правилах хорошего тона" при составлении запросов? Я, конечно, не знаю, всех тонкостей... В данном случае запрос выполняется, с нормальной скоростью, да ну и пусть. А в будущем может и пригодиться. Даже этот запрос подправлю