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.)