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.