MySQL Forums
Forum List  »  Optimizer & Parser

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

Written By
Re: Optimize - Using Temp, Using Filesort, Group By Having
April 13, 2011 07:56AM

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.