Consider this simple query:
SELECT rev_id, rev_timestamp, page_id, page_title, page_namespace FROM `revision` INNER JOIN `page` ON ((rev_page = page_id)) ORDER BY rev_timestamp DESC,rev_id DESC LIMIT 11;
I'd expect this to have a plan like this:
+------+-------------+----------+--------+----------------------------+---------------+---------+--------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+--------+----------------------------+---------------+---------+--------------------------+------+-------+ | 1 | SIMPLE | revision | index | rev_page_id,page_timestamp | rev_timestamp | 20 | NULL | 11 | | | 1 | SIMPLE | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.revision.rev_page | 1 | | +------+-------------+----------+--------+----------------------------+---------------+---------+--------------------------+------+-------+
and on my local machine (10.0.21-MariaDB-3) it does. But on all the WMF db slaves that I've tried, for some reason it insists on using this ridiculous query instead:
+------+-------------+----------+-------+----------------------------+----------------+---------+---------------------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+-------+----------------------------+----------------+---------+---------------------+----------+----------------------------------------------+ | 1 | SIMPLE | page | index | PRIMARY | name_title | 261 | NULL | 33836157 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | revision | ref | rev_page_id,page_timestamp | page_timestamp | 4 | enwiki.page.page_id | 8 | Using index | +------+-------------+----------+-------+----------------------------+----------------+---------+---------------------+----------+----------------------------------------------+
I can STRAIGHT_JOIN it to get it to use the sane plan, but IIRC we'd rather avoid STRAIGHT_JOIN these days.