При оптимизации запросов в mysql нельзя обойтись без лога медленных запросов. Для того, чтобы лог писался, добавляем в секцию [mysqld] конфигурационного файла my.cnf параметры:

• log_slow_queries указывает путь к логу медленных запросов,
• long_query_time — минимальное время в секундах для попадания запроса в лог.

Пример:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2

В этом случае все запросы, выполняющиеся 2 и более секунды, запишутся в файл /var/log/mysql/mysql-slow.log.

Для обработки и анализа лога медленных запросов предназначена консольная команда mysqldumpslow с таким синтаксисом: mysqldumpslow [options] [log_file ...].

mysqldumpslow группирует похожие запросы, заменяя числа и строки в запросах на N и 'S'.

Наиболее полезные опции это:

-s sort_type — тип сортировки запроса, обычно используются типы:
• t — по времени запроса,
• at — по среднему времени запроса,
• c — по количеству запросов;
• -t N — показывает только N запросов при выводе;
• -g pattern — фильтрует запросы по шаблону pattern (в стиле команды grep).

По умолчанию используется сортировка по среднему времени запроса -s at.

Например, чтобы получить 10 самых частых медленных запросов в файл top10-by-count.log:

mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log > top10-by-count.log

Источник