1

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?

2

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.

0

As far as the documentation for MySQL 5.7 - 5.4.5 The Slow Query Log is concerned...

To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexes system variable.

... and

log_queries_not_using_indexes

Whether queries that do not use indexes are logged to the slow query log. See Section 5.4.5, “The Slow Query Log”.

There is no mention to anything related to log_queries_using_table_scans, or anything similar. There are not mentions either in the Docs for MySQL 8 - 5.1.5 Server System Variables.

So, I'm afraid the answer is no, there is not an option coming (at least, as documented up until now). So, my guess is that you will have to log all slow queries, and find out later which ones do some table scans and which ones do not.

The mysqldumpslow utility might prove helpful. See also MySQL Slow Query Log: Tools and Tips and Tools for parsing a slow query log.

0

You can enable slow query log to get more details on slow queries.

slow_query_log=1
slow_query_log_file="queries-slow.log"
min_examined_row_limit=5000

The last setting, please tweak number of rows per your requirements. Start with a high number and reduce gradually. These settings can also be set using set global. In the log file you will see how many rows of a table being scanned. If the number is close to total number of records in the table, its a full table scan. This will allow you to pin point which queries are causing excessive number of rows being scanned.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.