CODEWITHSUNDEEP
mysqlsql-optimization
Sarath
Sarath

Reputation: 93

MySQL not using multiple column index

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'

Upvotes: 2

Views: 318

Answers (2)

Stephan
Stephan

Reputation: 8090

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

Upvotes: -1

jpmc26
jpmc26

Reputation: 29856

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.

Upvotes: 4

Related Questions