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.