Re: Avoiding "Using temporary; Using filesort"

[Danny Ó Cuív]

Sorry, I should really have added that my conclusion was that the query engine is always going to either restrict the publication_credits table on the basis of author_id first (i.e. do the WHERE) or do the join first. So it'll either use the index on author_id or publication_id. Once it's done that, the index on date from the publications table is no longer of any use as it indexes the full table. So it's going to have to be a filesort.

However some stuff I've read indicates that I should be able to optimize this to avoid the filesort. Some query reviewers report output with "Using temporary; Using filesort" as an error.

Thanks in advance,

Danny.