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