Bug #93845 Optimizer choose wrong index, sorting index instead of filtering index
Submitted: 7 Jan 2019 21:55 Modified: 9 Jan 2019 21:39
Reporter: Daniele Renda Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7,8.0.11,8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[7 Jan 2019 21:55] Daniele Renda
Description:
As detailed described here: https://stackoverflow.com/questions/53982463/mysql-picks-up-wrong-index-sorting-index-inst... I'm facing a performance issue filtering data.

I've this table:

CREATE TABLE `contactlens` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `createdBy` varchar(255) DEFAULT NULL,
  `createdDate` datetime(6) NOT NULL,
  `lastModifiedBy` varchar(255) DEFAULT NULL,
  `lastModifiedDate` datetime(6) DEFAULT NULL,
  `sid` varchar(36) NOT NULL,
  `version` bigint(20) NOT NULL,
  `brand` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `colorCode` varchar(255) DEFAULT NULL,
  `colorDescription` varchar(255) DEFAULT NULL,
  `description` longtext,
  `imageUrl` varchar(255) DEFAULT NULL,
  `lastPurchase` datetime(6) DEFAULT NULL,
  `lastPurchasePrice` decimal(19,2) DEFAULT NULL,
  `lastSell` datetime(6) DEFAULT NULL,
  `lastSellPrice` decimal(19,2) DEFAULT NULL,
  `line` varchar(255) DEFAULT NULL,
  `manufacturer` varchar(255) DEFAULT NULL,
  `manufacturerCode` varchar(255) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `preset` bit(1) NOT NULL DEFAULT b'0',
  `purchasePrice` decimal(19,2) DEFAULT NULL,
  `salesPrice` decimal(19,2) DEFAULT NULL,
  `sku` varchar(255) NOT NULL,
  `stock` bit(1) NOT NULL DEFAULT b'1',
  `thumbUrl` varchar(255) DEFAULT NULL,
  `trial` bit(1) NOT NULL DEFAULT b'0',
  `upc` varchar(255) DEFAULT NULL,
  `additionMax` decimal(10,2) DEFAULT NULL,
  `additionMin` decimal(10,2) DEFAULT NULL,
  `axisMax` int(11) DEFAULT NULL,
  `axisMin` int(11) DEFAULT NULL,
  `baseCurveMax` decimal(10,2) DEFAULT NULL,
  `baseCurveMin` decimal(10,2) DEFAULT NULL,
  `cylinderMax` decimal(10,2) NOT NULL,
  `cylinderMin` decimal(10,2) NOT NULL,
  `design` varchar(30) NOT NULL,
  `diameterMax` decimal(10,1) DEFAULT NULL,
  `diameterMin` decimal(10,1) DEFAULT NULL,
  `dominant` bit(1) DEFAULT NULL,
  `duration` int(11) NOT NULL,
  `family` varchar(30) DEFAULT NULL,
  `material` varchar(255) DEFAULT NULL,
  `pack` int(11) NOT NULL,
  `source` varchar(30) NOT NULL,
  `sphereMax` decimal(10,2) NOT NULL,
  `sphereMin` decimal(10,2) NOT NULL,
  `type` varchar(30) NOT NULL,
  `taxRate_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_sku` (`sku`),
  UNIQUE KEY `UK_elol05sqtuwi88exc8cdmqul1` (`sid`),
  UNIQUE KEY `idx_upc` (`upc`),
  KEY `idx_design` (`design`),
  KEY `FKq7sw02khmcn1nqil9pcxkgmfa` (`taxRate_id`),
  KEY `idx_manufacturer_line_duration_sph_cyl_add` (`type`,`design`,`line`,`duration`,`sphereMin`,`sphereMax`,`cylinderMin`,`cylinderMax`,`axisMin`,`axisMax`,`additionMin`,`additionMax`,`manufacturer`),
  KEY `idx_sorting` (`manufacturer`,`line`,`duration`,`sphereMin`,`cylinderMin`,`additionMin`),
  CONSTRAINT `FKq7sw02khmcn1nqil9pcxkgmfa` FOREIGN KEY (`taxRate_id`) REFERENCES `taxrate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2572246 DEFAULT CHARSET=utf8

and I'm running this query

SELECT *
FROM `ContactLens` contactlen0_
WHERE 1=1 
AND contactlen0_.`sphereMin`<=1.25 
AND contactlen0_.`sphereMax`>=1.75 
AND contactlen0_.`additionMin`<=2.25 
AND contactlen0_.`additionMax`>=2.5
AND contactlen0_.`type`='MULTI_FOCAL'
ORDER BY contactlen0_.`manufacturer` ASC, contactlen0_.`line` ASC, contactlen0_.`duration` ASC , contactlen0_.`sphereMin` ASC, contactlen0_.`cylinderMin` ASC, contactlen0_.`additionMin` ASC 
LIMIT 10

as you can see from EXPLAIN, Mysql is using idx_sorting (with 700k records takes 4 seconds) instead on the better index idx_manufacturer_line_duration_sph_cyl_add that would reduce the execution time.

How to repeat:
Run this query on the above table:

SELECT *
FROM `ContactLens` contactlen0_
WHERE 1=1 
AND contactlen0_.`sphereMin`<=1.25 
AND contactlen0_.`sphereMax`>=1.75 
AND contactlen0_.`additionMin`<=2.25 
AND contactlen0_.`additionMax`>=2.5
AND contactlen0_.`type`='MULTI_FOCAL'
ORDER BY contactlen0_.`manufacturer` ASC, contactlen0_.`line` ASC, contactlen0_.`duration` ASC , contactlen0_.`sphereMin` ASC, contactlen0_.`cylinderMin` ASC, contactlen0_.`additionMin` ASC 
LIMIT 10

This is the optimizer trace:
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `contactlen0_`.`id` AS `id`,`contactlen0_`.`createdBy` AS `createdBy`,`contactlen0_`.`createdDate` AS `createdDate`,`contactlen0_`.`lastModifiedBy` AS `lastModifiedBy`,`contactlen0_`.`lastModifiedDate` AS `lastModifiedDate`,`contactlen0_`.`sid` AS `sid`,`contactlen0_`.`version` AS `version`,`contactlen0_`.`brand` AS `brand`,`contactlen0_`.`category` AS `category`,`contactlen0_`.`colorCode` AS `colorCode`,`contactlen0_`.`colorDescription` AS `colorDescription`,`contactlen0_`.`description` AS `description`,`contactlen0_`.`imageUrl` AS `imageUrl`,`contactlen0_`.`lastPurchase` AS `lastPurchase`,`contactlen0_`.`lastPurchasePrice` AS `lastPurchasePrice`,`contactlen0_`.`lastSell` AS `lastSell`,`contactlen0_`.`lastSellPrice` AS `lastSellPrice`,`contactlen0_`.`line` AS `line`,`contactlen0_`.`manufacturer` AS `manufacturer`,`contactlen0_`.`manufacturerCode` AS `manufacturerCode`,`contactlen0_`.`name` AS `name`,`contactlen0_`.`preset` AS `preset`,`contactlen0_`.`purchasePrice` AS `purchasePrice`,`contactlen0_`.`salesPrice` AS `salesPrice`,`contactlen0_`.`sku` AS `sku`,`contactlen0_`.`stock` AS `stock`,`contactlen0_`.`thumbUrl` AS `thumbUrl`,`contactlen0_`.`trial` AS `trial`,`contactlen0_`.`upc` AS `upc`,`contactlen0_`.`additionMax` AS `additionMax`,`contactlen0_`.`additionMin` AS `additionMin`,`contactlen0_`.`axisMax` AS `axisMax`,`contactlen0_`.`axisMin` AS `axisMin`,`contactlen0_`.`baseCurveMax` AS `baseCurveMax`,`contactlen0_`.`baseCurveMin` AS `baseCurveMin`,`contactlen0_`.`cylinderMax` AS `cylinderMax`,`contactlen0_`.`cylinderMin` AS `cylinderMin`,`contactlen0_`.`design` AS `design`,`contactlen0_`.`diameterMax` AS `diameterMax`,`contactlen0_`.`diameterMin` AS `diameterMin`,`contactlen0_`.`dominant` AS `dominant`,`contactlen0_`.`duration` AS `duration`,`contactlen0_`.`family` AS `family`,`contactlen0_`.`material` AS `material`,`contactlen0_`.`pack` AS `pack`,`contactlen0_`.`source` AS `source`,`contactlen0_`.`sphereMax` AS `sphereMax`,`contactlen0_`.`sphereMin` AS `sphereMin`,`contactlen0_`.`type` AS `type`,`contactlen0_`.`taxRate_id` AS `taxRate_id` from `contactlens` `contactlen0_` where ((1 = 1) and (`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL')) order by `contactlen0_`.`manufacturer`,`contactlen0_`.`line`,`contactlen0_`.`duration`,`contactlen0_`.`sphereMin`,`contactlen0_`.`cylinderMin`,`contactlen0_`.`additionMin` limit 10"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((1 = 1) and (`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((1 = 1) and (`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL'))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((1 = 1) and (`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL'))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL'))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`contactlens` `contactlen0_`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`contactlens` `contactlen0_`",
                "field": "type",
                "equals": "'MULTI_FOCAL'",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`contactlens` `contactlen0_`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 728004,
                    "cost": 171586
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_sku",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "UK_elol05sqtuwi88exc8cdmqul1",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_upc",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "FKq7sw02khmcn1nqil9pcxkgmfa",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_manufacturer_line_duration_sph_cyl_add",
                      "usable": true,
                      "key_parts": [
                        "type",
                        "design",
                        "line",
                        "duration",
                        "sphereMin",
                        "sphereMax",
                        "cylinderMin",
                        "cylinderMax",
                        "axisMin",
                        "axisMax",
                        "additionMin",
                        "additionMax",
                        "manufacturer",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_sorting",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_design",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_manufacturer_line_duration_sph_cyl_add",
                        "ranges": [
                          "MULTI_FOCAL <= type <= MULTI_FOCAL"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 364002,
                        "cost": 436803,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`contactlens` `contactlen0_`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_manufacturer_line_duration_sph_cyl_add",
                      "rows": 364002,
                      "cost": 145601,
                      "chosen": true
                    },
                    {
                      "rows_to_scan": 728004,
                      "access_type": "scan",
                      "resulting_rows": 4492.1,
                      "cost": 171584,
                      "chosen": false
                    }
                  ]
                },
                "condition_filtering_pct": 1.2341,
                "rows_for_plan": 4492.1,
                "cost_for_plan": 145601,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`contactlens` `contactlen0_`",
                  "attached": "((`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5))"
                }
              ]
            }
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`contactlen0_`.`manufacturer`,`contactlen0_`.`line`,`contactlen0_`.`duration`,`contactlen0_`.`sphereMin`,`contactlen0_`.`cylinderMin`,`contactlen0_`.`additionMin`",
              "items": [
                {
                  "item": "`contactlen0_`.`manufacturer`"
                },
                {
                  "item": "`contactlen0_`.`line`"
                },
                {
                  "item": "`contactlen0_`.`duration`"
                },
                {
                  "item": "`contactlen0_`.`sphereMin`"
                },
                {
                  "item": "`contactlen0_`.`cylinderMin`"
                },
                {
                  "item": "`contactlen0_`.`additionMin`"
                }
              ],
              "resulting_clause_is_simple": true,
              "resulting_clause": "`contactlen0_`.`manufacturer`,`contactlen0_`.`line`,`contactlen0_`.`duration`,`contactlen0_`.`sphereMin`,`contactlen0_`.`cylinderMin`,`contactlen0_`.`additionMin`"
            }
          },
          {
            "added_back_ref_condition": "((`contactlen0_`.`type` <=> 'MULTI_FOCAL') and ((`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5)))"
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`contactlens` `contactlen0_`",
                "index_provides_order": true,
                "order_direction": "asc",
                "index": "idx_sorting",
                "plan_changed": true,
                "access_type": "index"
              }
            }
          },
          {
            "refine_plan": [
              {
                "table": "`contactlens` `contactlen0_`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

The behaviour seems the same on Mysql 5.7, 8.0.11 and 8.0.13. 

Also using ANALYZE UPDATE HISTOGRAMS does not improve the final result.

Suggested fix:
In my example most rows is of type MULTI_FOCAL. It also seems that the filtering from the conditions on the min/max columns is not taken into account when estimating how many rows needs to be read if using idx_sorting index.
[8 Jan 2019 13:47] MySQL Verification Team
Hi,

In order to proceed, we do require additional info. We need the output from that EXPLAIN statement that you are mentioning.

Second, we would like you to try forcing the "better" index and let us know the time it takes to sort rows. Do not forget that on the single table only one index can be used for the resolution of the query. That does not include various existing combo of indices, which are used only with filtering and not with sorting.

Last, when you prove the point of which index is faster, we need you to send us all data for the table, so that we can repeat your findings.
[9 Jan 2019 12:59] MySQL Verification Team
Hi,

Your report seems truly interesting.

Please, use "Files" tab, upload a dump of the table. Do not worry, these data will be accessible ONLY to Oracle employees.

Thanks in advance.
[9 Jan 2019 13:23] MySQL Verification Team
Hi,

When you manage to upload the ZIP file, let us know its filename and a directory on our SFTP where it is uploaded.

Thanks in advance.
[9 Jan 2019 13:26] MySQL Verification Team
Thanks, I will look for it ......
[9 Jan 2019 17:59] MySQL Verification Team
Hi,

On my system, a difference is much larger. This is the speed for the query without the FORCE INDEX:

real	2m7.975s
user	0m0.011s
sys	0m0.006s

Its EXPLAIN is like this:

+----+-------------+--------------+------------+-------+--------------------------------------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                              | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+--------------------------------------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | contactlen0_ | NULL       | index | idx_manufacturer_line_duration_sph_cyl_add | idx_sorting | 1556    | NULL |   22 |     0.56 | Using where |
+----+-------------+--------------+------------+-------+--------------------------------------------+-------------+---------+------+------+----------+-------------+

But, when the correct index is forced, execution time is 60 (sixty) times faster:

real	0m2.969s
user	0m0.013s
sys	0m0.010s

And its EXPLAIN is, of course, like this:

+----+-------------+--------------+------------+------+--------------------------------------------+--------------------------------------------+---------+-------+--------+----------+---------------------------------------+
| id | select_type | table        | partitions | type | possible_keys                              | key                                        | key_len | ref   | rows   | filtered | Extra                                 |
+----+-------------+--------------+------------+------+--------------------------------------------+--------------------------------------------+---------+-------+--------+----------+---------------------------------------+
|  1 | SIMPLE      | contactlen0_ | NULL       | ref  | idx_manufacturer_line_duration_sph_cyl_add | idx_manufacturer_line_duration_sph_cyl_add | 92      | const | 365162 |     1.23 | Using index condition; Using filesort |
+----+-------------+--------------+------------+------+--------------------------------------------+--------------------------------------------+---------+-------+--------+----------+---------------------------------------+

Hence, fully verified as reported.
[9 Jan 2019 21:39] Daniele Renda
Thanks for the update! I hope there will be a fix for the problem.
[10 Jan 2019 13:00] MySQL Verification Team
Hi,

I have changed the severity so that this bug gets attention.

When the bug is fixed, you will be notified on this page.
[18 Nov 2020 13:38] Rolf Martin-Hoster
I do not believe this is a bug, please review how compound indexes are processed https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html (this holds true for all versions of mysql afaik). I would recommend closing this bug.
[18 Nov 2020 13:48] MySQL Verification Team
Hi Mr. Martin-Hoster,

This is about a difference in the speed of the execution of the query. When we forced the usage of the other index, the performance improved tenfold.

That part is in the hidden comments, since internal data of the reporter was used to repeat the behaviour.

Thank you for your interest.
[20 Aug 2021 11:01] Vinieth S S
Any update reg this bug ?
[20 Aug 2021 12:02] MySQL Verification Team
Hi,

We shall check it out, but it might take some time .......
[20 Aug 2021 12:40] MySQL Verification Team
Hi!

Just to inform you all that this bug is temporarily fixed by the introduction of the optimiser switch: 

RECONSIDER_INDEX_FOR_ORDER

Hence, all you have to do is use it.
[20 Aug 2021 13:37] Vinieth S S
Hey thanks for the reply.
I don't see any optimiser switch in this name - RECONSIDER_INDEX_FOR_ORDER in mysql doc. Can you share the doc if possible, btw is it available in Mysql 5.7.18
[23 Aug 2021 11:47] MySQL Verification Team
Hi,

It is present only  in 8.0.
[23 Aug 2021 12:23] MySQL Verification Team
Sorry for the typo.

The correct name for that switch is:

 prefer_ordering_index

It is explained in the chapter 8.9.2.