MySQL doesn't use entire index when combining join and range

Question

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'

Answer 1

This is not a bug, but the way the optimizer was developed to perform, as was explained by Timour Katchaounov on the 28th February 2005:

This is not a bug, this is the way the system currently works (both 4.1 and 5.0), and this behavior is documented.

Generally there are three alternatives for queries with comparison operators:

1)
Use the "range access method". In this case this is not possible, because as Section "7.2.5.2. Range Access Method for Multiple-Part Indexes" says: " For a BTREE index, an interval might be usable for conditions combined with AND, where each condition compares a key part with a constant value using =, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN, or LIKE 'pattern' (where 'pattern' doesn't start with a wildcard)."
Notice the word "constant". In the example queries the equality compares values of two table attributes, not one attribute with a constant, so this method is not applicable.

2)
Use the "ref" access method. As Section "7.2.1. EXPLAIN Syntax (Get Information About a SELECT)" says:
"ref can be used for indexed columns that are compared using the = operator." Well, in this case we have inequalities, so the ref access method uses only the keyparts referenced in the equality, in this case only the first key part.

3)
Use "range checked for each record". As documented, this method will be used only if better than a full table scan, which is not the case with our small example.

The combination of 1) and 2) is a rather big new feature that will be implemented in some future version of MySQL.

You might want to follow up with the developers and find out if the feature has been (or will be) implemented in a future version of MySQL.

Answer 2

(Not an answer, but too much for a comment. And I want to pursue the issues.)

What versions did you test this with? (Not just "5.7", but "5.7.xx".)

Does EXPLAIN FORMAT=JSON SELECT ... provide more details?

Test this out, the numbers may be informative:

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

Digging through those numbers (which are exact, unlike EXPLAIN's), plus doing judicious SELECT COUNT(*)... queries, one can often infer a lot about what is going on during the execution of the query.

As a side note: Use VARCHAR, not CHAR unless the column is really fixed length. This will save a lot of space, hence some speed.