Re: Query performance on database with joins

[Rick James]

It will depend. If there are a lot of %drax% authors, the second one will be slower.

Further explanation (and thanks for using [ code ] )...

Note in the first explain:
* format is fully scanned (354 rows)
* module contributes about 1089 thingies FOR EACH FORMAT. -- It has now collected 354 * 1089 rows before getting around to filter on %drax%.

Your subquery went thru all 21K authors and found only 18. That made the rest fall in place without much further work.

You have a tough problem.
* %drax% requires a table scan -- slow, and gets slower as the table grows
* multi-table join
* ORDER BY...LIMIT can't easily be calculated from an index.