1
2
3
4
5
6
7
8
9

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `content` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a_index` (`a`) USING BTREE,
  KEY `b_a_index` (`b`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

delimiter  //
CREATE PROCEDURE init_test_table() 
BEGIN 
	DECLARE index_id INT;
	SET index_id = 0;
	START TRANSACTION; 
	WHILE index_id < 2000000 DO
		insert into test_table(a,b,content) values (0,0,CONCAT('content',index_id));
		SET index_id = index_id + 1;
	END WHILE;
	COMMIT; 
END //
delimiter ;
call init_test_table();

update test_table set b = 1 where id < 300000;
update test_table set a = 1 where id > 1700000;

1

select * from test_table where a = 0 and b = 0 limit 1;

1
2
3
4
5
6

mysql> explain select * from test_table where a = 0 and b = 0 limit 1;
+
| id | select_type | table      | partitions | type | possible_keys     | key     | key_len | ref   | rows   | filtered | Extra       |
+
|  1 | SIMPLE      | test_table | NULL       | ref  | a_index,b_a_index | a_index | 4       | const | 997354 |     8.08 | Using where |
+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> show profiles;
+----------+------------+-------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                         |
+----------+------------+-------------------------------------------------------------------------------+
|       49 | 0.44619325 | select * from test_table where a = 0 and b = 0 limit 1                        |
|       50 |   0.466321 | select * from test_table where a = 0 and b = 0 limit 1                        |
|       51 | 0.45312325 | select * from test_table where a = 0 and b = 0 limit 1                        |
|       52 |  0.4334895 | select * from test_table where a = 0 and b = 0 limit 1                        |
|       53 | 0.48915875 | select * from test_table where a = 0 and b = 0 limit 1                        |
|       54 |   0.000488 | select * from test_table force index(b_a_index) where a = 0 and b = 0 limit 1 |
|       55 |  0.0005185 | select * from test_table force index(b_a_index) where a = 0 and b = 0 limit 1 |
|       56 |  0.0006425 | select * from test_table force index(b_a_index) where a = 0 and b = 0 limit 1 |
|       57 | 0.00053575 | select * from test_table force index(b_a_index) where a = 0 and b = 0 limit 1 |
|       58 | 0.00069475 | select * from test_table force index(b_a_index) where a = 0 and b = 0 limit 1 |
+----------+------------+-------------------------------------------------------------------------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

"range_scan_alternatives": [
    {
        "index": "a_index",
        "ranges": [
            "0 <= a <= 0"
        ],
        "index_dives_for_eq_ranges": true,
        "rowid_ordered": true,
        "using_mrr": false,
        "index_only": false,
        "rows": 997354,
        "cost": 1200000,
        "chosen": true
    },
    {
        "index": "b_a_index",
        "ranges": [
            "0 <= b <= 0 AND 0 <= a <= 0"
        ],
        "index_dives_for_eq_ranges": true,
        "rowid_ordered": true,
        "using_mrr": false,
        "index_only": false,
        "rows": 997354,
        "cost": 1200000,
        "chosen": false,
        "cause": "cost"
    }
]

1
2
3

select * from test_table where a = 0 and b = 0 limit 1;
select * from test_table force index(b_a_index) where a = 0 and b = 0 limit 1
select * from test_table t1 right join (select id from test_table where a = 0 and b = 0 limit 1) t2 on t1.id = t2.id;

1
2
3
4
5
6

mysql> explain select * from test_table where a = 0 and b = 0 limit 1;
+
| id | select_type | table      | partitions | type | possible_keys     | key     | key_len | ref   | rows   | filtered | Extra       |
+
|  1 | SIMPLE      | test_table | NULL       | ref  | a_index,b_a_index | a_index | 4       | const | 997354 |     8.08 | Using where |
+

1
2
3
4
5
6
7
8

mysql> explain select t1.* from test_table t1 right join (select id from test_table where a = 0 and b = 0 limit 1) t2 on t1.id = t2.id;
+
| id | select_type | table      | partitions | type   | possible_keys     | key       | key_len | ref         | rows   | filtered | Extra       |
+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL              | NULL      | NULL    | NULL        |      1 |      100 | NULL        |
|  1 | PRIMARY     | t1         | NULL       | const  | PRIMARY           | PRIMARY   | 4       | const       |      1 |      100 | NULL        |
|  2 | DERIVED     | test_table | NULL       | ref    | a_index,b_a_index | b_a_index | 8       | const,const | 997354 |      100 | Using index |
+