MySQL Forums
Forum List  »  Performance

Re: understanding towards Using where; Using temporary; Using filesort is right?
Posted by: Rick James
Date: March 25, 2011 09:34AM

That's roughly correct.

Yes, MySQL always (almost always) walks through all (or part) of one table, reaching into the JOINed table using the "NLJ" method.

I ignore "Using where" as being noise that provides no useful information.

One issue... I don't think it will sort the temp table before joining to the next table. (But I don't have proof.) It will sometimes pick an INDEX on the first table so that the rows are fetched in the sorted order. But this is to _avoid_ the sort.

Note that "filesort" may not actually have to hit the disk. But it does mean that something (usually ORDER BY) required a sort of collected data before being able to deliver it (or part of it, in the case of LIMIT) to the client.

The interesting Extra is "Using index" which says that the query (or at least one of the tables of a JOIN) could be satisfied by looking only in an INDEX. Note: Indexes are stored separately from Data (except for the PRIMARY KEY in InnoDB). Rule of thumb: "Using index" doubles the speed.

"using temporary, using filesort" is often perceived as evil. In reality, it is often necessary because of the complexity of the query. Something like "GROUP BY xx ORDER BY yy" typically cannot be done without a temp table and a sort.

If you would like, present a particular EXPLAIN, together with SHOW CREATE TABLE(s), and we can discuss specifics. There are a zillion different cases.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: understanding towards Using where; Using temporary; Using filesort is right?
1294
March 25, 2011 09:34AM


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.