Geek asks and answers

Choosing the appropriate index among several indexes in a table in mysql does not occur

I have a MySql table with a hash index like

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 next query it does not take into account the 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;

he is considering the i5 index. why?

+3
source share
1 answer

during query execution mysql selects the best index for it from the set of indexes, which may not be the best at all. Therefore, to use a specific index, you can force / tell mysql to use it. see link

+1
source

More articles:


All Articles