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.dbWHERE ? LIKEdb`
I also tried querying the sys database directly but the same limitations exist there:
select * from sys.statements_with_full_table_scans
Any suggestions?
Thanks!
Best 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.