Skip navigation links

MySQL Forums :: Optimizer & Parser :: Optimize - Using Temp, Using Filesort, Group By Having


Advanced Search

Re: Optimize - Using Temp, Using Filesort, Group By Having
Posted by: Rick James ()
Date: April 13, 2011 07:56AM

Thanks for the readable layout of EXPLAIN.

Still need these, but I can probably do without:
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes

First row of EXPLAIN:
* "range" implies that it is using "... due_time <= ..."
* "14" -- I assume that is the combined size of `complete` and `due_time`, but it seems high.
* "87" -- estimate of rows with "complete = 'N' AND date_due <=..."; not a very big number.

Other rows of EXPLAIN:
* "ref" or "eq_ref" -- this implies a very efficient reaching into the other tables.
* "1" -- estimated (for ref) or exact (eq_ref) count of rows to read from those secondary tables.

Bottom line: It is a very efficient query. How long does it take? If the query takes more than, say, a second, then I need to see these to analyze why and what to do:
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW VARIABLES LIKE '%buffer%'; -- cache size

As for "Using Temp" and "Using Filesort" -- By having that compound query (and starting with it), it can now walk through the possible rows in the order required by ORDER BY, so it does not need to do a sort later.

BTW, the cost of "Using Temp" and "Using Filesort" is not that bad. For, say, 87 rows, the "temp" "file" would _probably_ be in RAM and never touch disk. And the "sort" would probably take microseconds, not milliseconds.

Options: ReplyQuote


Subject Views Written By Posted
Optimize - Using Temp, Using Filesort, Group By Having 3541 Lenny Dunn 04/08/2011 10:40AM
Re: Optimize - Using Temp, Using Filesort, Group By Having 1181 Rick James 04/09/2011 11:37PM
Re: Optimize - Using Temp, Using Filesort, Group By Having 917 Lenny Dunn 04/12/2011 01:38PM
Re: Optimize - Using Temp, Using Filesort, Group By Having 1488 Rick James 04/13/2011 07:56AM
Re: Optimize - Using Temp, Using Filesort, Group By Having 864 Lenny Dunn 04/13/2011 09:44AM
Re: Optimize - Using Temp, Using Filesort, Group By Having 1497 Rick James 04/14/2011 12:08AM
Re: Optimize - Using Temp, Using Filesort, Group By Having 1359 Lenny Dunn 04/19/2011 12:18PM
Re: Optimize - Using Temp, Using Filesort, Group By Having 802 Rick James 04/19/2011 10:40PM


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.