I'm trying to optimize a simple query that joins between two tables and applies a range condition. From the explain plan below, you can see that the index inv_quantity_on_hand is only used partially (4 bytes, only for the first column - inv_item_sk). I would expect the entire index to be used, as the second part (inv_quantity_on_hand) of the index is used in the WHERE clause in a range condition.
Please note that this happens only with a join and a range condition. Replacing the range condition to a constant equality comparison (inv_quantity_on_hand = 5) will change the explain plan and MySQL will use the entire index.
It seems to be an instance of this bug: https://bugs.mysql.com/bug.php?id=8569.
I checked it with MySQL 5.7.18 and the execution plan is still created the same way.
Can anyone think of a good workaround please?
Schema structure:
CREATE TABLE `inventory` (
`inv_date_sk` INT(11) NOT NULL,
`inv_item_sk` INT(11) NOT NULL,
`inv_warehouse_sk` INT(11) NOT NULL,
`inv_quantity_on_hand` INT(11) DEFAULT NULL,
PRIMARY KEY (`inv_date_sk` , `inv_item_sk` , `inv_warehouse_sk`),
KEY `inv_w` (`inv_warehouse_sk`),
KEY `inv_i` (`inv_item_sk`),
KEY `inv_quantity_on_hand_index` (`inv_item_sk` , `inv_quantity_on_hand`),
CONSTRAINT `inv_d` FOREIGN KEY (`inv_date_sk`)
REFERENCES `date_dim` (`d_date_sk`)
ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `inv_i` FOREIGN KEY (`inv_item_sk`)
REFERENCES `item` (`i_item_sk`)
ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `inv_w` FOREIGN KEY (`inv_warehouse_sk`)
REFERENCES `warehouse` (`w_warehouse_sk`)
ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB DEFAULT CHARSET=UTF8
CREATE TABLE `item` (
`i_item_sk` INT(11) NOT NULL,
`i_item_id` CHAR(16) NOT NULL,
`i_rec_start_date` DATE DEFAULT NULL,
`i_rec_end_date` DATE DEFAULT NULL,
`i_item_desc` VARCHAR(200) DEFAULT NULL,
`i_current_price` DECIMAL(7 , 2 ) DEFAULT NULL,
`i_wholesale_cost` DECIMAL(7 , 2 ) DEFAULT NULL,
`i_brand_id` INT(11) DEFAULT NULL,
`i_brand` CHAR(50) DEFAULT NULL,
`i_class_id` INT(11) DEFAULT NULL,
`i_class` CHAR(50) DEFAULT NULL,
`i_category_id` INT(11) DEFAULT NULL,
`i_category` CHAR(50) DEFAULT NULL,
`i_manufact_id` INT(11) DEFAULT NULL,
`i_manufact` CHAR(50) DEFAULT NULL,
`i_size` CHAR(20) DEFAULT NULL,
`i_formulation` CHAR(20) DEFAULT NULL,
`i_color` CHAR(20) DEFAULT NULL,
`i_units` CHAR(10) DEFAULT NULL,
`i_container` CHAR(10) DEFAULT NULL,
`i_manager_id` INT(11) DEFAULT NULL,
`i_product_name` CHAR(50) DEFAULT NULL,
PRIMARY KEY (`i_item_sk`),
KEY `item_color_index` (`i_color`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8
Query:
SELECT
*
FROM
inventory
INNER JOIN
item ON inventory.inv_item_sk = item.i_item_sk
WHERE
inventory.inv_quantity_on_hand > 100
AND item.i_color = 'red';
Execution plan:
# id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
-----+-------------+-----------+------------+------+----------------------------------+----------------------------+---------+----------------------+-----------------+-------------------------
1 | SIMPLE | item | | ref | PRIMARY,item_color_index | item_color_index | 61 | const | 384 | 100.00 |
1 | SIMPLE | inventory | | ref | inv_i,inv_quantity_on_hand_index | inv_quantity_on_hand_index | 4 | tpcds.item.i_item_sk | 615 | 33.33 | Using where; Using index
JSON execution plan:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "48745.23"
},
"nested_loop": [
{
"table": {
"table_name": "item",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"item_color_index"
],
"key": "item_color_index",
"used_key_parts": [
"i_color"
],
"key_length": "61",
"ref": [
"const"
],
"rows_examined_per_scan": 384,
"rows_produced_per_join": 384,
"filtered": "100.00",
"cost_info": {
"read_cost": "384.00",
"eval_cost": "76.80",
"prefix_cost": "460.80",
"data_read_per_join": "633K"
},
"used_columns": [
"i_item_sk",
"i_item_id",
"i_rec_start_date",
"i_rec_end_date",
"i_item_desc",
"i_current_price",
"i_wholesale_cost",
"i_brand_id",
"i_brand",
"i_class_id",
"i_class",
"i_category_id",
"i_category",
"i_manufact_id",
"i_manufact",
"i_size",
"i_formulation",
"i_color",
"i_units",
"i_container",
"i_manager_id",
"i_product_name"
]
}
},
{
"table": {
"table_name": "inventory",
"access_type": "ref",
"possible_keys": [
"inv_i",
"inv_quantity_on_hand_index"
],
"key": "inv_quantity_on_hand_index",
"used_key_parts": [
"inv_item_sk"
],
"key_length": "4",
"ref": [
"tpcds.item.i_item_sk"
],
"rows_examined_per_scan": 615,
"rows_produced_per_join": 78819,
"filtered": "33.33",
"using_index": true,
"cost_info": {
"read_cost": "987.83",
"eval_cost": "15763.96",
"prefix_cost": "48745.23",
"data_read_per_join": "1M"
},
"used_columns": [
"inv_date_sk",
"inv_item_sk",
"inv_warehouse_sk",
"inv_quantity_on_hand"
],
"attached_condition": "(`tpcds`.`inventory`.`inv_quantity_on_hand` > 100)"
}
}
]
}
}
Handlers counters in original query:
'Handler_commit', '1'
'Handler_delete', '0'
'Handler_discover', '0'
'Handler_external_lock', '4'
'Handler_mrr_init', '0'
'Handler_prepare', '0'
'Handler_read_first', '0'
'Handler_read_key', '385'
'Handler_read_last', '0'
'Handler_read_next', '249369'
'Handler_read_prev', '0'
'Handler_read_rnd', '0'
'Handler_read_rnd_next', '0'
'Handler_rollback', '0'
'Handler_savepoint', '0'
'Handler_savepoint_rollback', '0'
'Handler_update', '0'
'Handler_write', '0'
Handlers counters the same query without the range condition:
'Handler_commit', '1'
'Handler_delete', '0'
'Handler_discover', '0'
'Handler_external_lock', '4'
'Handler_mrr_init', '0'
'Handler_prepare', '0'
'Handler_read_first', '0'
'Handler_read_key', '385'
'Handler_read_last', '0'
'Handler_read_next', '249369'
'Handler_read_prev', '0'
'Handler_read_rnd', '0'
'Handler_read_rnd_next', '0'
'Handler_rollback', '0'
'Handler_savepoint', '0'
'Handler_savepoint_rollback', '0'
'Handler_update', '0'
'Handler_write', '0'
inv_quantity_on_hand_index2
?(
tpcds.
inventory.
inv_warehouse_sk` = 5), which does not show up in the query. The trick is to figure out how many rows it should have scanned for
inv_quantity_on_hand > 100` and compare that to what is in the Handler numbers. The problem is, that there is/was a JOIN going on.