Hi,
I'm kind of new to trying to figure out how the optimizer works. I have a fairly simple pair of tables. Stripping out the columns that are not relevant to this, I have:
publications
------------
id
date
publication_credits
-------------------
publication_id
author_id
id is a primary key, publication_id is a foreign key and there is an index on all four of these columns. I have a pretty straightforward query that finds all the publications for a given author, date-ordered:
select publications.* from publications inner join publication_credits on publications.id = publication_credits.publication_id where (publication_credits.author_id = 111) order by date desc;
The EXPLAIN output show that it puts publication_credits first in the join order (with type ref) followed by publications (with type eq_ref), that it considered using the indexes on publication_id and author_id, opted for the one on author_id and ultimately gave (under "Extra"): "Using where; Using temporary; Using filesort".
Some material on the web suggests that this is avoidable. So I tried a variety of things like adding additional indexes and even doing straight joins. I now think that sorting using filesort is unavoidable. To my mind, the following snippet from the "ORDER BY Optimization" section of the manual:
http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html
covers the issue:
"In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:
[other cases snipped]
You are joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)"
This seems to me to apply. Should I just accept this query or should it always be regarded as avoidable.
Thanks in advance,
Danny.