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`
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