On thistle with DB=dewiki:
mysql> explain select * from recentchanges left join tag_summary on ts_rc_id=rc_id order by rc_timestamp desc limit 50\G
*************************** 1. row *************************** table: recentchanges type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1179921 Extra: Using temporary; Using filesort *************************** 2. row *************************** table: tag_summary type: ALL possible_keys: ts_rc_id key: NULL key_len: NULL ref: NULL rows: 4 Extra: 2 rows in set (0.00 sec)
Whenever you do a join with a limit, MySQL gets the query plan wrong. It scans the small table and filesorts the large table. You have to use FORCE INDEX on the small table to suppress the scan. We've seen this many times. It's very difficult to detect during code review and frequently crashes the site.
Does anyone know a DBMS where joining with limits actually works? Because I'm sick of this crap.
-- Tim Starling