MySQL Bugs: #97667: order of index declaration improperly influences choice of index

Bug #97667	order of index declaration improperly influences choice of index
Submitted:	16 Nov 2019 19:26	Modified:	5 Feb 2020 16:06
Reporter:	Rick James	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6/5.7/8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	INDEX

Description:
The Optimizer seems to pick the first of multiple usable INDEXes even when not necessarily optimal.

Given these indexes:

    INDEX a_ts  (a, ts),  -- in the way
    INDEX a_b   (a, b)    -- clearly better

and this WHERE:

    WHERE a = 2 and b = 22

`a_ts` is picked instead of `a_b`, apparently only because it is first -- for both SELECT and DELETE.

Results of EXPLAIN:

mysql> EXPLAIN DELETE FROM inx_order WHERE a = 2 and b = 22;
+----+-------------+-----------+-------+---------------+------+---------+-------+------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | inx_order | range | a_ts,a_b      | a_ts | 8       | const |    2 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM inx_order WHERE a = 2 and b = 22;
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | inx_order | ref  | a_ts,a_b      | a_ts | 8       | const |    2 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Results when INDEX declarations are swapped:

mysql> EXPLAIN DELETE FROM inx_order WHERE a = 2 and b = 22;
+----+-------------+-----------+-------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | inx_order | range | a_b,a_ts      | a_b  | 12      | const,const |    2 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM inx_order WHERE a = 2 and b = 22;
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref         | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------+
|  1 | SIMPLE      | inx_order | ref  | a_b,a_ts      | a_b  | 12      | const,const |    2 | NULL  |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS inx_order;
CREATE TABLE inx_order (
    id INT AUTO_INCREMENT,
    a BIGINT NOT NULL,
    b INT NOT NULL,
    ts TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY(id),
    INDEX a_ts  (a, ts),  -- in the way
    INDEX a_b   (a, b)    -- clearly better
);

INSERT INTO inx_order (a,b) VALUES
    (1,11), (1,11), (4,11),
    (2,22), (2,22), (5,22);

EXPLAIN DELETE FROM inx_order WHERE a = 2 and b = 22;
EXPLAIN SELECT * FROM inx_order WHERE a = 2 and b = 22;

DROP TABLE IF EXISTS inx_order;
CREATE TABLE inx_order (
    id INT AUTO_INCREMENT,
    a BIGINT NOT NULL,
    b INT NOT NULL,
    ts TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY(id),
    INDEX a_b   (a, b),   -- clearly better
    INDEX a_ts  (a, ts)   -- out of the way
);

INSERT INTO inx_order (a,b) VALUES
    (1,11), (1,11), (4,11),
    (2,22), (2,22), (5,22);

EXPLAIN DELETE FROM inx_order WHERE a = 2 and b = 22;
EXPLAIN SELECT * FROM inx_order WHERE a = 2 and b = 22;

Suggested fix:
more Optimizer work.

Thank you for the bug report.

Posted by developer:

[19 Nov 2019 5:17] Sergey Glukhov

Here is output for the query
'SELECT * FROM inx_order WHERE a = 2 and b = 22':
 
---
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`inx_order`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "a_ts",
                      "rows": 2,
                      "cost": 0.7,
                      "chosen": true
                    },
                    {
                      "access_type": "ref",
                      "index": "a_b",
                      "rows": 2,
                      "cost": 0.7,
                      "chosen": false
                    }
---
 
Both the indexes return the same number of rows
(there are only two records with 'a=2') and the same cost.
Both the indexes are not covered. The first index is chosen
because the second one does not do any better than the first one.
So current behavior is correct.

Grrr...  I must have over-simplified the test case.  The real table has millions of rows and runs significantly faster with one index.  (Of course, I shun using FORCE INDEX for production systems -- "while it helps today, it may hurt tomorrow".)  I'll try to add some rows to demonstrate the problem.