MySQL Forums
Forum List  »  Optimizer & Parser

Don't dread "Using temporary; Using filesort"
Posted by: Rick James
Date: July 18, 2011 08:08PM

innodb_buffer_pool_size | 8388608
Read and heed http://mysql.rjweb.org/doc.php/memory
Even 20M would help this query -- your tables are tiny.

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

"Filesort" probably leads to creating a temp table using the MEMORY Engine; this is very efficient. For 1 row, we are talking about on the order of a millisecond. The "filesort" would be a C call to quicksort in memory (unless it optimizes the case of 1 row).

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

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

select  p.*
    from  publications
    inner join  publication_credits AS pc on p.id = pc.publication_id
    where  (pc.author_id = 111)
    order by  p.date desc;
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).

Options: ReplyQuote


Subject
Views
Written By
Posted
Don't dread "Using temporary; Using filesort"
1854
July 18, 2011 08:08PM


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.