MySQL slow query log to identify queries that scan tables

execution-planlogMySQLperformancequery-performance

MySQL seems not to have setting to show you queries that use SOME indexes but still also do table scans

We have some badly performing queries and were hoping to find them systematically but the log_queries_not_using_indexes setting seems only to look for queries that use NO INDEXES AT ALL, rather than using full table scans.

Anyone to confirm? Is there a new option coming?

Best Answer

I find log_queries_not_using_indexes to be useless. More useful is the queries that take a long time; this will include any queries that do some or all table scans and are slow, but exclude trivial table scans that are fast.

If a table has 5 rows, it does not really matter whether it uses an index or does a table scan -- the speed will be virtually the same.

These start with the most important queries to work on. I suggest you work on the first 3, then wait for the impact to happen and get new slowlog:

mysqldumpslow -s t   # to sort by most impact first
pt-query-digest

Also, long_query_time=1 is probably a good starting point.

(I keep my ear to the ground; I have not heard of the option you suggest. You could file a "feature request" with bugs.mysql.com.)

More

The Optimizer sometimes decides that a table scan is likely to be faster than using an index. This happens when it estimates that more than 20% of the rows will be touched. (The "20" varies with the phases of the moon.) This is because of bouncing between the BTree of a secondary index and the data BTree has some cost.

Related Solutions

Mysql – Slow insert with MySQL full-text index

It must be as quick if not quicker to do:

ALTER TABLE mydb.datafulltxt DISABLE KEYS;
Do many INSERTs into mydb.datafulltxt
ALTER TABLE mydb.datafulltxt ENABLE KEYS;
Repeat as required.

MySQL query optimizer ignoring smaller scan on TIMESTAMP column, cardinality

For these queries and if your WHERE is as you have shown and you also have ORDER BY rf_timestamp you can use this index, which should be far better than a single index on si_id or a single index on rf_timestamp:

ALTER TABLE rf
  ADD INDEX si_id__rf_timestamp__IX         -- choose a name for the index
    (si_id, rf_timestamp) ;

With a table of this size, adding this index will take some time and the table will be locked in the mean time, so it would be better if you did this when there is not much traffic and work by others in the database.

Best Answer

Related Solutions

Mysql – Slow insert with MySQL full-text index

MySQL query optimizer ignoring smaller scan on TIMESTAMP column, cardinality

Related Question