|
Индексы в подзапросах
#39241887
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Доброго времени суток всем.
Есть такой запрос:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select `assist`.`name`, `no` as `DT_RowId`, `surname`, `crt_date`, `user_countries`.`name` as `country_name`, `user_cities`.`name` as `city_name`, `user_clients`.`name` as `client`, `policy_no`,
`case_status`.`status_name`, `case_type`.`name` as `case_type`, IFNULL(GROUP_CONCAT(DISTINCT user_cards.name SEPARATOR ','),'Unset') as prov
from `assist`
left join `user_countries` on `assist`.`country` = `user_countries`.`id`
left join `user_cities` on `assist`.`city` = `user_cities`.`id`
left join `user_clients` on `assist`.`client_id` = `user_clients`.`code`
left join `case_status` on `assist`.`status` = `case_status`.`id`
left join `case_type` on `assist`.`type` = `case_type`.`id`
left join `case_providers` on `assist`.`no` = `case_providers`.`case_no`
left join `user_cards` on `case_providers`.`code` = `user_cards`.`code`
group by `assist`.`no`
order by `assist`.`no` desc limit 25 offset 0
explain
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
+----+-------------+----------------+------------+--------+---------------+--------------+---------+----------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+--------+---------------+--------------+---------+----------------------------+------+----------+-------------+
| 1 | SIMPLE | assist | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 18 | 100.00 | NULL |
| 1 | SIMPLE | user_countries | NULL | eq_ref | PRIMARY | PRIMARY | 4 | assist.assist.country | 1 | 100.00 | NULL |
| 1 | SIMPLE | user_cities | NULL | eq_ref | PRIMARY | PRIMARY | 4 | assist.assist.city | 1 | 100.00 | NULL |
| 1 | SIMPLE | user_clients | NULL | eq_ref | code | code | 4 | assist.assist.client_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | case_status | NULL | eq_ref | PRIMARY | PRIMARY | 4 | assist.assist.status | 1 | 100.00 | NULL |
| 1 | SIMPLE | case_type | NULL | eq_ref | PRIMARY | PRIMARY | 4 | assist.assist.type | 1 | 100.00 | NULL |
| 1 | SIMPLE | case_providers | NULL | ref | unique_index | unique_index | 4 | assist.assist.no | 1 | 100.00 | Using index |
| 1 | SIMPLE | user_cards | NULL | eq_ref | code | code | 4 | assist.case_providers.code | 1 | 100.00 | NULL |
+----+-------------+----------------+------------+--------+---------------+--------------+---------+----------------------------+------+----------+-------------+
Для создания постраничного списка считаю общее количество строк таким образом:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select count(*) as aggregate
from
(select `assist`.`name`, `no` as `DT_RowId`, `surname`, `crt_date`, `user_countries`.`name` as `country_name`, `user_cities`.`name` as `city_name`, `user_clients`.`name` as `client`,
`policy_no`, `case_status`.`status_name`, `case_type`.`name` as `case_type`, IFNULL(GROUP_CONCAT(DISTINCT user_cards.name SEPARATOR ',
'),'Unset') as prov
from `assist`
left join `user_countries` on `assist`.`country` = `user_countries`.`id`
left join `user_cities` on `assist`.`city` = `user_cities`.`id`
left join `user_clients` on `assist`.`client_id` = `user_clients`.`code`
left join `case_status` on `assist`.`status` = `case_status`.`id`
left join `case_type` on `assist`.`type` = `case_type`.`id`
left join `case_providers` on `assist`.`no` = `case_providers`.`case_no`
left join `user_cards` on `case_providers`.`code` = `user_cards`.`code`
group by `assist`.`no` order by `assist`.`no` desc)
count_row_table
и его explain
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
+----+-------------+----------------+------------+--------+---------------+--------------+---------+----------------------------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+--------+---------------+--------------+---------+----------------------------+-------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 53421 | 100.00 | NULL |
| 2 | DERIVED | assist | NULL | ALL | PRIMARY | NULL | NULL | NULL | 40308 | 100.00 | Using filesort |
| 2 | DERIVED | user_countries | NULL | eq_ref | PRIMARY | PRIMARY | 4 | assist.assist.country | 1 | 100.00 | NULL |
| 2 | DERIVED | user_cities | NULL | eq_ref | PRIMARY | PRIMARY | 4 | assist.assist.city | 1 | 100.00 | NULL |
| 2 | DERIVED | user_clients | NULL | eq_ref | code | code | 4 | assist.assist.client_id | 1 | 100.00 | NULL |
| 2 | DERIVED | case_status | NULL | eq_ref | PRIMARY | PRIMARY | 4 | assist.assist.status | 1 | 100.00 | NULL |
| 2 | DERIVED | case_type | NULL | eq_ref | PRIMARY | PRIMARY | 4 | assist.assist.type | 1 | 100.00 | NULL |
| 2 | DERIVED | case_providers | NULL | ref | unique_index | unique_index | 4 | assist.assist.no | 1 | 100.00 | Using index |
| 2 | DERIVED | user_cards | NULL | eq_ref | code | code | 4 | assist.case_providers.code | 1 | 100.00 | NULL |
+----+-------------+----------------+------------+--------+---------------+--------------+---------+----------------------------+-------+----------+----------------+
Собственно второй запрос и не устраивает, точнее время его выполнения >3s. Можно ли его как-то ускорить? |
|
|
