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