MySQL – Log Non-Indexed Queries for Multiple Databases

indexindex-tuningMySQL

I have about 50 MySQL databases, each with more than 20 tables. My Sort_rows, handler_read_rnd_next, and handler_read_first stats are really high.

I can't add indexes manually for every table if not necessary, and could miss some, so I'm looking for a tool or a configuration which could help me to find which rows / requests have missing indexes.

Which tools are you using, or how should I configure MySQL to tell me more about these non indexed queries?

Best Answer

To find all queries not using indexes, you need to do two things:

  1. Enable the slow query log
  2. Set the global variable log_queries_not_using_indexes to ON

In my.cnf for 5.6, this would look like:

[mysqld]
...
...
slow_query_log=ON
log_queries_not_using_indexes=ON

Both are dynamic variables and can be set on a running system like this:

mysql> SET GLOBAL slow_query_log=ON;
mysql> SET GLOBAL log_queries_not_using_indexes=ON;

Refer to https://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html for full details and note that by default queries not using indexes are not logged to the slow query log, but if log_queries_not_using_indexes is enabled, ALL queries not using indexes will be logged, regardless of if they take longer than 'long_query_time' to execute. If the number of queries logged is excessive, use log_throttle_queries_not_using_indexes to limit the number of queries not using indexes that will be logged per minute. Like the other two global variables, this is dynamic and can be changed on a running system like this, e.g. to set it as 10 per minute:

mysql> SET GLOBAL log_throttle_queries_not_using_indexes=10;

That ought to fix you up.

Related Question