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?
To find all queries not using indexes, you need to do two things:
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.
long_query_time=1; turn on the slowlog - to FILE if possible; wait a day or more; use pt-query-digest to summarize the results.
The 'worst' queries will be sorted (by default) first in the output. Work on the first few.
Repeat periodically.
I do not like log_queries_not_using_indexes=ON -- it just clutters the slowlog with tiny tables where it does not really matter whether an index is used or not. Furthermore, there are times where the Optimizer deliberately and correctly eschews all indexes and does a table scan.
I say there is no way to automatically generate indexes. After all, sometimes a 'composite' index is the 'right' answer. Sometimes reformulating the query is better than anything an index could do. Sometimes a schema change is the best action. Sometimes (rarely these days), some VARIABLE will help. Sometimes improvement involves a change to the app.
The Handler% entries in SESSION STATUS are handy, but I limit use of them to comparing one formulation of the query to another. It is very good at saying, even for tiny tables, which query will scale better.
If you have any queries that are hard to improve on, post a new Question. Include SHOW CREATE TABLE and EXPLAIN SELECT ...
See also my Index Cookbook. Included in it is something that a lot of DBAs get less-than-optimal -- the CREATE TABLE for a many:many mapping table. It's an example of having an AUTO_INCREMENT PRIMARY KEY hurting performance.
(I've been doing this sort of stuff for over a decade.)
I sure this question was many times asked there
JetProfiler & MONyog allow concentrate on most important queries, sort loading by database, total time, total number, lock time and etc, save data for feature analysis and compare ...
