MySQL: find full table scan queries

Posted on

Question :

Using MySQL 5.6.24.

What is the best way to extract the text of queries using full-table scans? The “Full Table Scans” report in MySQL Workbench doesn’t include the complete query (some text is replaced with …), for example:

SELECT `Db` AS `scope` , SYSTE ... sql` . `db` WHERE ? LIKE `db`

I also tried querying the sys database directly but the same limitations exist there:

select * from sys.`statements_with_full_table_scans`

Any suggestions?

Answer :

Queries that do “full table scan” are the ones that don’t use indexes. Try to log them in the slow query log using this option log_queries_not_using_indexes

Be careful though that small tables that have frequent queries running against will fill your slow query log files. You may want to enable this option for limited amount of time. To reduce this chance, you may set min_examined_row_limit variable to a reasonable value, depending on your small tables.

You can use view with full query

select query, exec_count
from sys.x$statements_with_full_table_scans

Leave a Reply

Your email address will not be published. Required fields are marked *