Re: SQL Script to get the queries which are using full table scan

[Rick James]

A full table scan is just one of many things that can hurt performance. I prefer to grab all naughty queries at once:

Set long_query_time=1
Turn off log_queries_not_using_indexes -- it just clutters the output.
Turn on the slowlog.
Wait a day.
Use pt-query-digest to summarize the slowlog. (mysqldumpslow -s t is another way).

Then work on the first few that it lists. Some may be missing indexes. Some may have full table scans because of the lack of an index. Some may have justifiable full table scans. A common problem is lack of an appropriate composite index.

For Data Warehousing, the main problem is "Reports" that take a long time. The main solution is "Summary tables". Performance tools will only tell you that the query is a problem. They may give hints of how to improve it. But the real solution is to build and maintain a summary table.

Here is my discussion on Summary Tables:
http://mysql.rjweb.org/doc.php/summarytables
And DW:
http://mysql.rjweb.org/doc.php/datawarehouse

Show me some details; I may have more specific advice.