MySQL Forums
Forum List  »  Optimizer & Parser

Re: Don't dread "Using temporary; Using filesort"
Posted by: Danny Ó Cuív
Date: July 20, 2011 07:27AM

That's fantastic, Rick. That's exactly the kind of information I was seeking. Thank you so much.

Carefully read and (hopefully) heeded. When you say 70% of _available_ RAM, I take it you mean as reported on the "-/+ buffers/cache" line of the output from free? (i.e. ignoring buffers and cache).


The EXPLAIN says Rows = 1 and 1, as if there is only one row needed from each table.

I picked a dummy ID off the top of my head and picked badly.


The point is, "filesort" and "using temporary" are necessary, and not be dreaded.

That's exactly what I was leaning towards. A query reviewer I was using reported it as an error. But what I read in the MySQL documentation led me to believe it should not be considered an error.


You have author_id and date being NULLable; can it really be NULL?

Neither can be null. I'm collaborating on this. With a certain web framework that shall not be mentioned, some developers feel that the validation should be in the application layer and not replicated in the database. Suffice it to say that I disagree.


The cause of the "filesort" is probably that you are filtering on one table, then collecting data from another, and finally need to sort.

Your query is reasonably well optimized, and your indexes are reasonable (based on what I have seen so far).

Perfect. Thanks.


Options: ReplyQuote

Written By
Re: Don't dread "Using temporary; Using filesort"
July 20, 2011 07:27AM

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.