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).
Quote
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.
Quote
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.
Quote
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.
Quote
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.
Danny.