MySQL not using multiple column index

Question

I have query with columns in where clause which are part of primary key and have foreign key indexes on all columns.

EXPLAIN SELECT   aggEI.c_id AS companyId, aggEI.ei_uid AS uuid
FROM AGG_EI AS aggEI 
WHERE  aggEI.c_id in (8) and aggEI.tg_id IN (1,2,3,4,5,6,7)
       AND aggEI.dt_id = 20130506

I have also defined multiple columnn index on (c_id,tg_id,dt_id) but EXPLAIN shows that it is using foreign key index on c_id

 1 SIMPLE   aggEI   ref PRIMARY,datedm_id_UNIQUE,agg_ei_comdm_fk_idx,agg_ei_datedm_fk_idx,agg_ei_topgrp_fk_idx,comp_uuid    agg_ei_comdm_fk_idx 8   const   65986   Using where; Using index

agg_ei_comdm_fk_idx is the foreign key index on c_id and comp_uuid is the multiple column index on (c_id,tg_id,dt_id)

Can someone explain why it is happening like this

EDIT: Create table

          'CREATE TABLE `AGG_EI` (
              `dt_id` int(11) NOT NULL,
              `c_id` bigint(20) NOT NULL,
              `tg_id` bigint(20) NOT NULL,
              `ei_uid` varchar(150) NOT NULL
              `ei_name` varchar(150) NOT NULL,
               `rating` double NOT NULL,
              `cnt` double NOT NULL
               PRIMARY KEY (`dt_id`,`c_id`,`tg_id`,`ei_uid`),
                UNIQUE KEY `datedm_id_UNIQUE` (`dt_id`,`c_id`,`ei_uid`,`tg_id`),
               KEY `agg_ei_comdm_fk_idx` (`c_id`),
               KEY `agg_ei_datedm_fk_idx` (`dt_id`),
               KEY `agg_ei_topgrp_fk_idx` (`tg_id`),
               KEY `comp_uuid` (`c_id`,`tg_id`,`dt_id`),
               CONSTRAINT `agg_ei_comdm_fk` FOREIGN KEY (`c_id`) REFERENCES `COMPDM`     (`c_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
                CONSTRAINT `agg_ei_datedm_fk` FOREIGN KEY (`dt_id`) REFERENCES `DATEDM` (`dt_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
                CONSTRAINT `agg_ei_topgrp_fk` FOREIGN KEY (`tg_id`) REFERENCES `TOPGRP` (`tg_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
             ) ENGINE=InnoDB DEFAULT CHARSET=utf8'

Try changing aggEI.c_id in (8) to aggEI.c_id = 8 It may use the primary index then. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 23, 2013 at 8:27
@ypercube in general there can be many c_id's aggEI.c_id in (8,2,4) — Sarath
– Sarath, Commented May 23, 2013 at 9:09

jpmc26 · Accepted Answer · 2013-05-23 08:08:50Z

4

MySQL is performing the query using the plan that it thinks will perform the best. More than likely, it determined that limiting c_id to a single value reduced the result set to few enough rows that going to the trouble of dealing with the extra columns in the other index wasn't worth it. MySQL's best guess is that it's faster to limit the rows using just c_id and then just filter those rows in memory. Just because you have an index doesn't mean that's the fastest plan.

answered May 23, 2013 at 8:08

jpmc26

30.2k14 gold badges100 silver badges152 bronze badges

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

Comments

Stephan · Accepted Answer · 2013-05-23 08:36:25Z

-1

You can force mysql to use a index like :

EXPLAIN 
SELECT   
    aggEI.c_id AS companyId, 
    aggEI.ei_uid AS uuid
FROM 
    AGG_EI AS aggEI FORCE INDEX(`comp_uuid`)
WHERE  
    aggEI.c_id in (8) 
    AND aggEI.tg_id IN (1,2,3,4,5,6,7)
    AND aggEI.dt_id = 20130506

answered May 23, 2013 at 8:36

Stephan

8,1103 gold badges40 silver badges42 bronze badges

2 Comments

Stephan Over a year ago

if you use the wrong index yes but if you are sure about want index you need then it will be a performance boost ... like in this case. BTW what is the output from my query ?

Stephan Over a year ago

I'm glad i could help :) do not forget to choose an accepted answer , cheers

Collectives™ on Stack Overflow

MySQL not using multiple column index

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related