root@db1176.eqiad.wmnet[mediabackups]> EXPLAIN select count(*) as count, wiki_name, status_name, backup_status_name FROM files JOIN wikis ON files.wiki = wikis.id JOIN file_status ON files.status = file_status.id JOIN backup_status ON backup_status.id = files.backup_status WHERE wiki_name IN ('nlwiki', 'nowiki', 'plwiki', 'ptwiki', 'svwiki', 'thwiki', 'trwiki', 'zhwiki') GROUP BY wiki, status, backup_status; +------+-------------+---------------+--------+-------------------------------------------------------------------+---------+---------+-------- | id | select_type | table | type | possible_keys | key | key_len | ref +------+-------------+---------------+--------+-------------------------------------------------------------------+---------+---------+-------- | 1 | SIMPLE | file_status | ALL | PRIMARY | NULL | NULL | NULL | 1 | SIMPLE | files | ref | status,backup_status,wiki_status_backup_status,wiki_backup_status | status | 2 | mediaba | 1 | SIMPLE | wikis | eq_ref | PRIMARY,wiki_name | PRIMARY | 4 | mediaba | 1 | SIMPLE | backup_status | ALL | PRIMARY | NULL | NULL | NULL +------+-------------+---------------+--------+-------------------------------------------------------------------+---------+---------+-------- 4 rows in set (0.001 sec) root@db1176.eqiad.wmnet[mediabackups]> EXPLAIN select count(*) as count, wiki_name, status_name, backup_status_name FROM files FORCE INDEX(wiki_backup_status) JOIN wikis ON files.wiki = wikis.id JOIN file_status ON files.status = file_status.id JOIN backup_status ON backup_status.id = files.backup_status WHERE wiki_name IN ('nlwiki', 'nowiki', 'plwiki', 'ptwiki', 'svwiki', 'thwiki', 'trwiki', 'zhwiki') GROUP BY wiki, status, backup_status; +------+-------------+---------------+--------+--------------------+--------------------+---------+----------------------------------+------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | E +------+-------------+---------------+--------+--------------------+--------------------+---------+----------------------------------+------+-- | 1 | SIMPLE | wikis | range | PRIMARY,wiki_name | wiki_name | 258 | NULL | 8 | U | 1 | SIMPLE | files | ref | wiki_backup_status | wiki_backup_status | 4 | mediabackups.wikis.id | 9336 | U | 1 | SIMPLE | file_status | ALL | PRIMARY | NULL | NULL | NULL | 4 | U | 1 | SIMPLE | backup_status | eq_ref | PRIMARY | PRIMARY | 1 | mediabackups.files.backup_status | 1 | +------+-------------+---------------+--------+--------------------+--------------------+---------+----------------------------------+------+-- 4 rows in set (0.001 sec) root@db1176.eqiad.wmnet[mediabackups]> select count(*) as count, wiki_name, status_name, backup_status_name FROM files FORCE INDEX(wiki_backup_status) JOIN wikis ON files.wiki = wikis.id JOIN file_status ON files.status = file_status.id JOIN backup_status ON backup_status.id = files.backup_status WHERE wiki_name IN ('nlwiki', 'nowiki', 'plwiki', 'ptwiki', 'svwiki', 'thwiki', 'trwiki', 'zhwiki') GROUP BY wiki, status, backup_status; +--------+-----------+-------------+--------------------+ | count | wiki_name | status_name | backup_status_name | +--------+-----------+-------------+--------------------+ | 20 | nlwiki | public | pending | | 126 | nlwiki | archived | pending | | 75442 | nlwiki | deleted | pending | | 5 | nowiki | public | pending | | 72 | nowiki | archived | pending | | 5195 | nowiki | deleted | pending | | 261 | plwiki | public | pending | | 58 | plwiki | archived | pending | | 54521 | plwiki | deleted | pending | | 57566 | ptwiki | public | pending | | 1533 | ptwiki | archived | pending | | 43113 | ptwiki | deleted | pending | | 38 | svwiki | archived | pending | | 12605 | svwiki | deleted | pending | | 27676 | thwiki | public | pending | | 3216 | thwiki | archived | pending | | 68382 | thwiki | deleted | pending | | 36417 | trwiki | public | pending | | 4181 | trwiki | archived | pending | | 98013 | trwiki | deleted | pending | | 57871 | zhwiki | public | pending | | 719 | zhwiki | archived | pending | | 134198 | zhwiki | deleted | pending | +--------+-----------+-------------+--------------------+ 23 rows in set (1.279 sec)