DBMS where join+limit works? - Wikitech-l

27 Feb 2009

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