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?


| improve this question | | | | |
  • A "full table scan" is not necessarily evil. Nor is it always avoidable. I would focus on whatever is "slow" in the slowlog. Some of them will involve full table scans; some full table scans won't be slow enough to show up there. – Rick James Sep 12 '15 at 22:27

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.

| improve this answer | | | | |
  • Hi. So, "full table scan" is not the opposite of ones "that don't use indexes". We have a situation where some queries that are holding things up ARE using some indexes, but the problem is they are ALSO doing table scans. And there is no MySQL setting that finds these. I know of a bad query that is doing a table scan as well as indexes and it does not show up in this log with that setting. – user2415376 Jul 2 '17 at 16:01

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.