We have a table called Feedback as follows :

id            - int(10) unsigned    NO  PRI     auto_increment vendor_Id     - int(10) unsigned    YES MUL      created_At    - datetime CURRENT_TIMESTAMP   updated_At    - datetime(3)          code          - int(11) YES      feedback      - varchar(300)    YES 

and we need to create index for our Feedback Table.

So we added 3 indexes based on fields as part of where clause :

 index1 (vendor_Id, updated_At);  codeIndex (vendor_Id, updated_At, code)  feedbackIndex(vendor_Id, code, feedback, updated_At) 

Now when we run the following query :

explain select * from Feedback where vendor_Id = 1 and updated_At < '2017-11-15 12:58:12.387' and updated_At >= '2017-11-01 00:00:00.000' and code is not null and code >=0 and code <= 6 order by updated_At DESC limit 10 

the mysql output says :

possible_keys : index1,codeIndex,feedbackIndex keys : feedbackIndex 

Am curious to know why the explain statement shows ‘feedbackIndex’ as the key selected. We were expecting it to select ‘codeIndex’

There are other 2 queries :

 explain select code, count(*) as count from Feedback where vendor_Id = 1 and updated_At < '2017-11-15 12:58:12.387' and updated_At >= '2017-11-01 00:00:00.000' and code is not null group by code 

===> shows ‘codeIndex’ as key in output of explain statement. which is as expected

 explain select code, count(*) as count from Feedback where vendor_Id = 1 and updated_At < '2017-11-15 12:58:12.387' and updated_At >= '2017-11-01 00:00:00.000' and code is not null and feedback is not null group by code`  

===> shows ‘feedbackIndex’ as key in output of explain statement. which is as expected

I can not figure out why our first query is showing feedbackIndex as the keys.