Some queries from Web interface aren't using indexes requiring too much time on v3.1.3

Beginne damit, die Weboberflächenabfrage state=*\u0026archive=true nachzustellen und das mitgelieferte SQL mit EXPLAIN gegen MySQL auszuführen. Verfolge den Abfragepfad, der diese Anweisung erzeugt, und überprüfe anschließend, dass die Abfrage geeignete Indizes verwendet und laut slow log nicht mehr als 90 Sekunden benötigt.

This query: state=*&archive=true
Is producing a complex sql statement that it is not using indexes and are taking a long time to complete:

explain SELECT (coalesce((SELECT count FROM patchwork_patchtag WHERE patchwork_patchtag.patch_id=patchwork_patch.id AND patchwork_patchtag.tag_id=1), 0)) AS `tag_1_count`, (coalesce((SELECT count FROM patchwork_patchtag WHERE patchwork_patchtag.patch_id=patchwork_patch.id AND patchwork_patchtag.tag_id=2), 0)) AS `tag_2_count`, (coalesce((SELECT count FROM patchwork_patchtag WHERE patchwork_patchtag.patch_id=patchwork_patch.id AND patchwork_patchtag.tag_id=3), 0)) AS `tag_3_count`, (coalesce((SELECT count FROM patchwork_patchtag WHERE patchwork_patchtag.patch_id=patchwork_patch.id AND patchwork_patchtag.tag_id=4), 0)) AS `tag_4_count`, (coalesce((SELECT count FROM patchwork_patchtag WHERE patchwork_patchtag.patch_id=patchwork_patch.id AND patchwork_patchtag.tag_id=5), 0)) AS `tag_5_count`, `patchwork_patch`.`id`, `patchwork_patch`.`msgid`, `patchwork_patch`.`date`, `patchwork_patch`.`submitter_id`, `patchwork_patch`.`project_id`, `patchwork_patch`.`name`, `patchwork_patch`.`delegate_id`, `patchwork_patch`.`state_id`, `patchwork_patch`.`series_id`, `patchwork_person`.`id`, `patchwork_person`.`email`, `patchwork_person`.`name`, `patchwork_person`.`user_id`, `auth_user`.`id`, `auth_user`.`password`, `auth_user`.`last_login`, `auth_user`.`is_superuser`, `auth_user`.`username`, `auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`is_staff`, `auth_user`.`is_active`, `auth_user`.`date_joined`, `patchwork_state`.`id`, `patchwork_state`.`name`, `patchwork_state`.`slug`, `patchwork_state`.`ordering`, `patchwork_state`.`action_required`, `patchwork_series`.`id`, `patchwork_series`.`name` FROM `patchwork_patch` INNER JOIN `patchwork_person` ON (`patchwork_patch`.`submitter_id` = `patchwork_person`.`id`) LEFT OUTER JOIN `auth_user` ON (`patchwork_patch`.`delegate_id` = `auth_user`.`id`) LEFT OUTER JOIN `patchwork_state` ON (`patchwork_patch`.`state_id` = `patchwork_state`.`id`) LEFT OUTER JOIN `patchwork_series` ON (`patchwork_patch`.`series_id` = `patchwork_series`.`id`) WHERE (`patchwork_patch`.`project_id` = 1 AND `patchwork_patch`.`archived`) ORDER BY `patchwork_patch`.`date` DESC LIMIT 1000;
+------+--------------------+--------------------+--------+----------------------------------------------------------------------+--------------------------+---------+----------------------------------------+-------+-----------------------------+
| id   | select_type        | table              | type   | possible_keys                                                        | key                      | key_len | ref                                    | rows  | Extra                       |
+------+--------------------+--------------------+--------+----------------------------------------------------------------------+--------------------------+---------+----------------------------------------+-------+-----------------------------+
|    1 | PRIMARY            | patchwork_patch    | ref    | patchwork_patch_499df97c,patchwork_patch_1a37f020,patch_covering_idx | patchwork_patch_499df97c | 4       | const                                  | 30607 | Using where; Using filesort |
|    1 | PRIMARY            | patchwork_person   | eq_ref | PRIMARY                                                              | PRIMARY                  | 4       | patchwork.patchwork_patch.submitter_id | 1     |                             |
|    1 | PRIMARY            | auth_user          | eq_ref | PRIMARY                                                              | PRIMARY                  | 4       | patchwork.patchwork_patch.delegate_id  | 1     | Using where                 |
|    1 | PRIMARY            | patchwork_state    | eq_ref | PRIMARY                                                              | PRIMARY                  | 4       | patchwork.patchwork_patch.state_id     | 1     | Using where                 |
|    1 | PRIMARY            | patchwork_series   | eq_ref | PRIMARY                                                              | PRIMARY                  | 4       | patchwork.patchwork_patch.series_id    | 1     | Using where                 |
|    6 | DEPENDENT SUBQUERY | patchwork_patchtag | eq_ref | patch_id                                                             | patch_id                 | 8       | patchwork.patchwork_patch.id,const     | 1     |                             |
|    5 | DEPENDENT SUBQUERY | patchwork_patchtag | eq_ref | patch_id                                                             | patch_id                 | 8       | patchwork.patchwork_patch.id,const     | 1     |                             |
|    4 | DEPENDENT SUBQUERY | patchwork_patchtag | eq_ref | patch_id                                                             | patch_id                 | 8       | patchwork.patchwork_patch.id,const     | 1     |                             |
|    3 | DEPENDENT SUBQUERY | patchwork_patchtag | eq_ref | patch_id                                                             | patch_id                 | 8       | patchwork.patchwork_patch.id,const     | 1     |                             |
|    2 | DEPENDENT SUBQUERY | patchwork_patchtag | eq_ref | patch_id                                                             | patch_id                 | 8       | patchwork.patchwork_patch.id,const     | 1     |                             |
+------+--------------------+--------------------+--------+----------------------------------------------------------------------+--------------------------+---------+----------------------------------------+-------+-----------------------------+

As reported by mysql slow log, it takes more than 1:30 mins to complete:

# Thread_id: 5213  Schema: patchwork  QC_hit: No
# Query_time: 92.553408  Lock_time: 0.000355  Rows_sent: 1000  Rows_examined: 101124
# Rows_affected: 0  Bytes_sent: 367857

Issue noticed at https://patchwork.linuxtv.org.