Bug #98054 MySQL Optimizer Bug not picking right index
Submitted: 23 Dec 2019 11:24 Modified: 23 Dec 2019 12:11
Reporter: sudheer gadipathi Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.7 OS:Red Hat (amezon RDS)
Assigned to: CPU Architecture:Any
Tags: mysql indexes, mysql optimizer

[23 Dec 2019 11:24] sudheer gadipathi
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.
[23 Dec 2019 11:38] sudheer gadipathi
It is a duplicate bug please close it.
[23 Dec 2019 12:11] MySQL Verification Team
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

https://bugs.mysql.com/bug.php?id=98055