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.