Page MenuHomePhabricator
Paste P17801

mariadb query planner for mediabackups
ActivePublic

Authored by jcrespo on Nov 23 2021, 12:27 PM.
Tags
None
Referenced Files
F34763252: mariadb query planner for mediabackups
Nov 23 2021, 12:27 PM
Subscribers
None
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)