Description:
Table structure:
CREATE TABLE `ht` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`rid` bigint(20) DEFAULT NULL,
`sm` tinyint(1) DEFAULT '0',
`dd` tinyint(1) DEFAULT '0',
`aid` bigint(20) NOT NULL DEFAULT '0',
`did` bigint(20) DEFAULT NULL,
`gid` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`,`aid`),
UNIQUE KEY `index_ht_aid_and_did` (`aid`,`did`),
KEY `index_ht_on_aid_and_gid` (`aid`,`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=13129695630 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY HASH (`aid`)
PARTITIONS 128 */;
Query:
SELECT COUNT(*) FROM ht f WHERE ht.`aid` = 209058 AND (gid IN (0,6000200468)) and gid IN ('6000200468') AND ht.sm = 0 AND ht.dd = 0 AND ht.sm = 0 AND ht.dd = 0 )\G
Problem :
There is (aid,did) unique key and it is picking even there is no did column in the where clause. (aid,gid) columns are in where clause and there is an index with them but not picking it even we run analyze and optimize .
Selecting wring index even there is no did in there where clause:
mysql> explain
-> SELECT
-> COUNT(*)
-> FROM
-> ht
-> WHERE
-> ht.`aid` = 209058
-> AND (gid
-> IN (0,6000200468))
-> AND ( ht.gid
-> IN ('6000200468')
-> AND ht.sm = 0
-> AND ht.dd = 0
-> AND ht.sm = 0
-> AND ht.dd = 0 )\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ht
partitions: p34
type: ref
possible_keys: index_ht_aid_and_did,index_ht_on_aid_and_gid
key: index_ht_aid_and_did
key_len: 8
ref: const
rows: 5641273
filtered: 0.10
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> SELECT
-> COUNT(*)
-> FROM
-> ht
-> WHERE
-> ht.`aid` = 209058
-> AND (gid
-> IN (0,6000200468))
-> AND ( ht.gid
-> IN ('6000200468')
-> AND ht.sm = 0
-> AND ht.dd = 0
-> AND ht.sm = 0
-> AND ht.dd = 0 )\G
*************************** 1. row ***************************
COUNT(*): 5056319
1 row in set (30.59 sec)
With right index:
mysql> explain
-> SELECT
-> COUNT(*)
-> FROM
-> ht force index (index_helpdesk_tickets_on_aid_and_gid)
-> WHERE
-> ht.`aid` = 209058
-> AND (gid
-> IN (0,6000200468))
-> AND ( ht.gid
-> IN ('6000200468')
-> AND ht.sm = 0
-> AND ht.dd = 0
-> AND ht.sm = 0
-> AND ht.dd = 0 )\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ht
partitions: p34
type: ref
possible_keys: index_ht_on_aid_and_gid
key: index_ht_on_aid_and_gid
key_len: 17
ref: const,const
rows: 5641273
filtered: 1.00
Extra: Using index condition; Using where
1 row in set, 1 warning (0.01 sec)
mysql> SELECT COUNT(*) FROM ht force index (index_helpdesk_tickets_on_aid_and_gid) WHERE ht.`aid` = 209058 AND (gid IN (0,6000200468)) Agid IN ('6000200468') AND ht.sm = 0 AND ht.dd = 0 AND ht.sm = 0 AND ht.dd = 0 )\G
*************************** 1. row ***************************
COUNT(*): 5056319
1 row in set (9.77 sec)
How to repeat:
you can reproduce it with random values.
Suggested fix:
It looks like there needs to fix this optimizer bug for this usecase.