appropriate index selection among multiple index's on a table in mysql is not happening

Question

I have a MySql table with a hash index's, like this

CREATE TABLE `forecast_item_store` (
`product_key` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`store_key` int(11) DEFAULT NULL,
`collection` varchar(45) DEFAULT NULL,
`category_key` int(11) DEFAULT NULL,
`flag` int(5) DEFAULT NULL,
`directly` tinyint(1) DEFAULT '1',
KEY `product_key_fr_idx` (`product_key`),
KEY `store_key_frr_idx` (`store_key`,`parent_id`,`product_key`),
KEY `product_key_parent_id` (`product_key`,`parent_id`),
KEY `i1` (`product_key`,`flag`,`store_key`,`parent_id`) USING HASH,
KEY `i2` (`product_key`,`store_key`,`parent_id`) USING HASH,
KEY `i3` (`product_key`,`flag`,`parent_id`) USING HASH,
KEY `i5` (`category_key`,`product_key`,`parent_id`) USING HASH,
KEY `i4` (`product_key`,`flag`,`category_key`,`parent_id`) USING HASH,
KEY `i` (`category_key`,`product_key`,`flag`,`parent_id`) USING HASH,
CONSTRAINT `product_key_fr` FOREIGN KEY (`product_key`) REFERENCES `dim_product`  (`product_key`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `store_key_frr` FOREIGN KEY (`store_key`) REFERENCES `dim_store`  (`store_key`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

for the following query it is not considering index,i

select 
    count(store_key)
from
    forecast_item_store
where
    category_key = 1 and product_key = 8981
        and flag = 1
        and parent_id = 2759;

it is considering index i5. why?

Answer 1

while running a query, mysql picks the best index for it from a bunch of indices, which might not be the best at all. So in order to use a particular index you can force/tell mysql to use it. see the link