How to optimize mysql query using index?

Question

I have following query which is taking 3 seconds to run.

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);

explain plan for the query is :

---+---------+-------+-------+----------------------------------------------+
| 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 |

Please help me out to optimize this query.

what you get for the query just the number of rows 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 — Abhik Chakraborty
– Abhik Chakraborty, Commented Nov 14, 2014 at 7:32

Amadan · Accepted Answer · 2014-11-14 07:31:09Z

1

ALTER TABLE cellwise_performance ADD INDEX (kpi_name, kpi_value, only_date);

You need an index that catches all the columns you are searching by, or it can't optimise everything.

answered Nov 14, 2014 at 7:31

Amadan

200k23 gold badges252 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

krokodilko · Accepted Answer · 2014-11-15 07:23:31Z

1

Ok I did something like that and query working like a charm -

select x.kpi,x.rating,date(x.date) as month,Count(*) `cnt` 
from ( select c.kpi_name as kpi ,
              case when c.kpi_value<2 then 0 
                   when c.kpi_value >= 5 then 2 
                   else 1 
              end `rating` ,
              c.only_date as date 
       from cellwise_performance c 
       where c.kpi_name ='DL Cell Throughput (Mbps)' 
         and c.only_date>='2014-06-10') as `x` 
 group by x.kpi,x.rating,month(x.date);

edited Nov 15, 2014 at 7:23

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

answered Nov 14, 2014 at 8:55

Aamir

7583 gold badges18 silver badges43 bronze badges

Collectives™ on Stack Overflow

How to optimize mysql query using index?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related