Description:
Query :
This query has two possible keys ,idx_01 and idx_02 ,MySQL optimizer choose the idx_01 for this query ,because the idx_01 cost is less than idx_02 .
Actually choose idx_02 is better and more fast than idx_01 ,because idx_02 is a cover index .So I think MySQL optimizer choose a wrong index .
mysql> show create table info2 \G;
*************************** 1. row ***************************
Table: info2
Create Table: CREATE TABLE `info2` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`agent_id` int unsigned NOT NULL DEFAULT '0',
`order_code` varchar(10) NOT NULL DEFAULT '',
`issue` char(12) NOT NULL DEFAULT '',
`order_number` varchar(700) NOT NULL DEFAULT '',
`state` tinyint unsigned NOT NULL DEFAULT '0',
`is_test` bit(1) NOT NULL DEFAULT b'0',
`created_date` date NOT NULL DEFAULT '1970-01-01',
PRIMARY KEY (`id`),
KEY `idx_01` (`issue`),
KEY `idx_02` (`order_code`,`issue`,`state`,`order_number`),
KEY `idx_03` (`agent_id`),
KEY `idx_05` (`created_date`,`is_test`,`order_code`,`state`)
) ENGINE=InnoDB AUTO_INCREMENT=98000002131317 DEFAULT CHARSET=utf8mb3
1 row in set (0.08 sec)
mysql> analyze table info2;
+-------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| jtest.info2 | analyze | status | OK |
+-------------+---------+----------+----------+
1 row in set (0.22 sec)
mysql> explain analyze
-> select count(1)
-> from info2
-> where order_code = '1027'
-> and issue = '20220720'
-> and state = 0 ;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(1) (cost=35405.89 rows=1) (actual time=17378.759..17378.760 rows=1 loops=1)
-> Filter: ((info2.state = 0) and (info2.order_code = '1027')) (cost=34346.18 rows=10597) (actual time=306.768..17351.605 rows=426873 loops=1)
-> Index lookup on info2 using idx_01 (issue='20220720') (cost=34346.18 rows=1059712) (actual time=1.489..17092.348 rows=2000011 loops=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (17.46 sec)
mysql> explain analyze
-> select count(1)
-> from info2 force index(idx_02)
-> where order_code = '1027'
-> and issue = '20220720'
-> and state = 0 ;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(1) (cost=289747.99 rows=1) (actual time=935.811..935.812 rows=1 loops=1)
-> Covering index lookup on info2 using idx_02 (order_code='1027', issue='20220720', state=0) (cost=201099.79 rows=886482) (actual time=0.052..916.064 rows=426873 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.94 sec)
How to repeat:
1.Create the info2 table .
2.Import the info2.sql file .
3.Run the two queries :
explain analyze
select count(1)
from info2
where order_code = '1027'
and issue = '20220720'
and state = 0 ;
explain analyze
select count(1)
from info2 force index(idx_02)
where order_code = '1027'
and issue = '20220720'
and state = 0 ;
Description: Query : This query has two possible keys ,idx_01 and idx_02 ,MySQL optimizer choose the idx_01 for this query ,because the idx_01 cost is less than idx_02 . Actually choose idx_02 is better and more fast than idx_01 ,because idx_02 is a cover index .So I think MySQL optimizer choose a wrong index . mysql> show create table info2 \G; *************************** 1. row *************************** Table: info2 Create Table: CREATE TABLE `info2` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `agent_id` int unsigned NOT NULL DEFAULT '0', `order_code` varchar(10) NOT NULL DEFAULT '', `issue` char(12) NOT NULL DEFAULT '', `order_number` varchar(700) NOT NULL DEFAULT '', `state` tinyint unsigned NOT NULL DEFAULT '0', `is_test` bit(1) NOT NULL DEFAULT b'0', `created_date` date NOT NULL DEFAULT '1970-01-01', PRIMARY KEY (`id`), KEY `idx_01` (`issue`), KEY `idx_02` (`order_code`,`issue`,`state`,`order_number`), KEY `idx_03` (`agent_id`), KEY `idx_05` (`created_date`,`is_test`,`order_code`,`state`) ) ENGINE=InnoDB AUTO_INCREMENT=98000002131317 DEFAULT CHARSET=utf8mb3 1 row in set (0.08 sec) mysql> analyze table info2; +-------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------+---------+----------+----------+ | jtest.info2 | analyze | status | OK | +-------------+---------+----------+----------+ 1 row in set (0.22 sec) mysql> explain analyze -> select count(1) -> from info2 -> where order_code = '1027' -> and issue = '20220720' -> and state = 0 ; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Aggregate: count(1) (cost=35405.89 rows=1) (actual time=17378.759..17378.760 rows=1 loops=1) -> Filter: ((info2.state = 0) and (info2.order_code = '1027')) (cost=34346.18 rows=10597) (actual time=306.768..17351.605 rows=426873 loops=1) -> Index lookup on info2 using idx_01 (issue='20220720') (cost=34346.18 rows=1059712) (actual time=1.489..17092.348 rows=2000011 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (17.46 sec) mysql> explain analyze -> select count(1) -> from info2 force index(idx_02) -> where order_code = '1027' -> and issue = '20220720' -> and state = 0 ; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Aggregate: count(1) (cost=289747.99 rows=1) (actual time=935.811..935.812 rows=1 loops=1) -> Covering index lookup on info2 using idx_02 (order_code='1027', issue='20220720', state=0) (cost=201099.79 rows=886482) (actual time=0.052..916.064 rows=426873 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.94 sec) How to repeat: 1.Create the info2 table . 2.Import the info2.sql file . 3.Run the two queries : explain analyze select count(1) from info2 where order_code = '1027' and issue = '20220720' and state = 0 ; explain analyze select count(1) from info2 force index(idx_02) where order_code = '1027' and issue = '20220720' and state = 0 ;