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

Start by reproducing the Web interface query state=*&archive=true and running the supplied SQL with EXPLAIN against MySQL. Trace the query path that generates this statement, then verify that the query uses suitable indexes and no longer takes more than 90 seconds according to the slow log.

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.