У меня есть следующий запрос, выполнение которого занимает 3 секунды.
SELECT c.kpi_name as kpi ,count(*) as cnt ,c.only_date as date
FROM cellwise_performance c
WHERE c.kpi_name ='DL Cell Throughput (Mbps)'
AND c.only_date BETWEEN '2014-06-10' AND '2014-11-05' AND c.kpi_value>=5
GROUP BY c.kpi_name,month(c.only_date)
union all
SELECT c.kpi_name as kpi ,count(*) as cnt ,c.only_date as date
FROM cellwise_performance c
WHERE c.kpi_name ='DL Cell Throughput (Mbps)'
AND c.only_date BETWEEN '2014-06-10' AND '2014-11-05' AND c.kpi_value between 2 and 5
GROUP BY c.kpi_name,month(c.only_date)
union all
SELECT c.kpi_name as kpi ,count(*) as cnt ,c.only_date as date
FROM cellwise_performance c
WHERE c.kpi_name ='DL Cell Throughput (Mbps)'
AND c.only_date BETWEEN '2014-06-10' AND '2014-11-05' AND c.kpi_value<2
GROUP BY c.kpi_name,month(c.only_date);
объясните план запроса:
---+---------+-------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------+------+------------------------------+----------+---------+-------+-------+----------------------------------------------+
| 1 | PRIMARY | c | ref | kpi_name,kpi_value,only_date | kpi_name | 203 | const | 72552 | Using where; Using temporary; Using filesort |
| 2 | UNION | c | ref | kpi_name,kpi_value,only_date | kpi_name | 203 | const | 72552 | Using where; Using temporary; Using filesort |
| 3 | UNION | c | ref | kpi_name,kpi_value,only_date | kpi_name | 203 | const | 72552 | Using where; Using temporary; Using filesort |
| NULL | UNION RESULT | <union1,2,3> | ALL | NULL | NULL | NULL | NULL | NULL |
Пожалуйста, помогите мне оптимизировать этот запрос.
select count(*) from cellwise_performance where kpi_name ='DL Cell Throughput (Mbps)'AND only_date between '2014-06-10'and '2014-11-05' and kpi_value>=5
- person Abhik Chakraborty   schedule 14.11.2014