MyISAM Performance: Join Decomposition

breaking up complex queries into simple ones

Poppycock. Why do extra effort when MySQL is quite willing to do it for you? As for performance -- there is probably no difference except that the broken up queries require more round trips to the server.

OTOH, there are cases where you can outsmart the optimizer. But your example was not one of those.

IN (thousands-of-ids) is possible, but painful, for the server. It will sort and de-dup them, then leave them in some kind of structure for repeated binary searching. I have seen lots of such queries, but only those over, say, 50K items raised any eyebrows.

There are times when this rewrite helps:

SELECT ... ORDER BY ... LIMIT ...

-->

SELECT b... 
FROM tbl b 
   JOIN ( SELECT id FROM TBL WHERE ... ORDER BY ... LIMIT ... ) a 
   ON a.id = b.id 

But that is to avoid hauling around extra junk that will be thrown away by the LIMIT.

I have done this in a few places. Doing multiple simple queries and building an ID list in the application logic, even with the ID list containing 10,000+ ID's made significant performance increases. The table I was querying had around 5 million records and doing a JOIN was painfully slow. After switching to using IN with an ID list it took about 1% of the time the JOIN took.