1

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'
4
  • What is in inv_quantity_on_hand_index2?
    – Rick James
    Aug 30, 2017 at 17:43
  • Sorry about that, it was an index left from one of the tests I did to analyze this issue. I dropped it now to revert to the original state and re-fetched the JSON execution plan (updated in the question).
    – Tomer Shay
    Aug 31, 2017 at 19:02
  • Well... the JSON still has (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.
    – Rick James
    Aug 31, 2017 at 21:42
  • I apologize (again) for the misleading information. I corrected the execution plan. Thank you for your suggestion, I will further investigate and will update if I'll come up with anything interesting. Thank you!
    – Tomer Shay
    Sep 1, 2017 at 6:23

2 Answers 2

2

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.

1
  • Thanks. I looked into that explanation in the Bug's page, but I'm not sure how would one should approach to optimizing such a query anyway. Joining to a table and having a range condition on it sounds like a very common use case to me. It just doesn't make sense to me that there is no good workaround to optimize such queries.
    – Tomer Shay
    Aug 29, 2017 at 8:01
0

(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.

1
  • Hi Rick, thank you for your comment. I'm using MySQL 5.7.18. I looked at the JSON explain and the handlers counters (with and without the range condition) and I can't see any indication of index usage for the range part. It seems that the handlers counters are the same for the query with the range and without. I added the relevant information to the question above. Will be glad to hear any further thoughts. Thanks!
    – Tomer Shay
    Aug 30, 2017 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.