MySQL Forums
Forum List  »  Backup

Re: SQL Script to get the queries which are using full table scan
Posted by: Rick James
Date: March 16, 2016 05:12PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: SQL Script to get the queries which are using full table scan
990
March 16, 2016 05:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.