Note, I think I'm encountering this bug which was marked as "Can't replicate": https://bugs.mysql.com/bug.php?id=97709
That said, I'm interested if anyone has a workaround to query performance that seems to affect tables that have JSON columns. This table has roughly 45 million rows within it.
CREATE TABLE `loop` (
`post_id` varchar(255) NOT NULL,
`type_id` varchar(125) NOT NULL,
`partner_id` varchar(10) DEFAULT NULL,
`market_id` varchar(100) DEFAULT NULL,
`user_id` varchar(100) DEFAULT NULL,
`resources` json DEFAULT NULL,
`template_id` varchar(255) DEFAULT NULL,
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
`deleted` datetime DEFAULT NULL,
`likes` json DEFAULT NULL,
`subscribers` json DEFAULT NULL,
`last_event_date` datetime DEFAULT NULL,
`icon` json DEFAULT NULL,
`_version` bigint(20) DEFAULT NULL,
PRIMARY KEY (`type_id`,`post_id`),
KEY `idx_loop_prod_timestamps` (`partner_id`,`type_id`,`created`,`deleted`,`updated`),
KEY `idx_loop_prod` (`created` DESC,`partner_id`,`type_id`,`market_id`,`user_id`,`deleted`,`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
A query that looks as follows returns in less than 80ms:
SELECT
type_id, post_id
FROM
the_loop.loop
WHERE
(partner_id = 'VMF'
AND type_id IN ('PostType-275e2809-5658-48ba-b3cf-0884f5851347' , 'PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba',
'PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301',
'PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9',
'PostType-1cdac073-9cfd-4388-98ca-86b140921adb',
'PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')
AND created BETWEEN '2021-02-09 00:00:00'
AND created < '2021-08-08 23:59:59.999'
AND deleted IS NULL)
ORDER BY created DESC
LIMIT 25 OFFSET 0;
A count of this same query takes over a minute to return (9 million ish rows):
SELECT
count(*)
FROM
the_loop.loop
WHERE
(partner_id = 'VMF'
AND type_id IN ('PostType-275e2809-5658-48ba-b3cf-0884f5851347' , 'PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba',
'PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301',
'PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9',
'PostType-1cdac073-9cfd-4388-98ca-86b140921adb',
'PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')
AND created BETWEEN '2021-02-09 00:00:00'
AND created < '2021-08-08 23:59:59.999'
AND deleted IS NULL)
When using MySQL 5.7 with the same table structure, same data and same index the query returns within 3ish seconds.
EDIT 1:
Here is the explain statement for the above query:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "4672714.02"
},
"table": {
"table_name": "loop",
"access_type": "range",
"possible_keys": [
"PRIMARY",
"idx_loop_prod_timestamps",
"idx_loop_prod",
"idx_loop_prod_type_id_partner_id_created_deleted"
],
"key": "PRIMARY",
"used_key_parts": [
"type_id"
],
"key_length": "502",
"rows_examined_per_scan": 23298882,
"rows_produced_per_join": 7,
"filtered": "0.00",
"cost_info": {
"read_cost": "4672713.29",
"eval_cost": "0.72",
"prefix_cost": "4672714.02",
"data_read_per_join": "24K"
},
"used_columns": [
"type_id",
"partner_id",
"created",
"deleted"
],
"attached_condition": "((`the_loop`.`loop`.`partner_id` = 'VMF') and (`the_loop`.`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`the_loop`.`loop`.`created` between '2021-02-09 00:00:00' and `the_loop`.`loop`.`created`) < '2021-08-08 23:59:59.999') and (`the_loop`.`loop`.`deleted` is null))"
}
}
}
Based upon the above explain I just created a new index:
ALTER TABLE `the_loop`.`loop`
ADD INDEX `idx_loop_prod_type_id_partner_id_created_deleted` (`type_id` ASC, `partner_id` ASC, `created` ASC, `deleted` ASC) VISIBLE;
I feel like I'm missing something very basic here as the new index doesn't seem to affect performance if when using use/force index hints.
EDIT 2:
Here is the optimizer trace for the above count query. At this point I'm mostly thinking the linked oracle bug report has some validity to it.
SELECTcount(*)FROMthe_loop.loopWHERE(partner_id='VMF'ANDtype_idIN('PostType-275e2809-5658-48ba-b3cf-0884f5851347',
'PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba',
'PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301',
'PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9',
'PostType-1cdac073-9cfd-4388-98ca-86b140921adb',
'PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')ANDcreatedBETWEEN'2021-02-09 00:00:00'ANDcreated<'2021-08-08 23:59:59.999'ANDdeletedISNULL)LIMIT0,
1000,
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"IN_uses_bisection": true
},
{
"expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `loop` where ((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null)) limit 0,1000"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null) and multiple equal('VMF', `loop`.`partner_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null) and multiple equal('VMF', `loop`.`partner_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null) and multiple equal('VMF', `loop`.`partner_id`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`loop`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`loop`",
"field": "partner_id",
"equals": "'VMF'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`loop`",
"range_analysis": {
"table_scan": {
"rows": 25495539,
"cost": 8.92e6
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"type_id",
"post_id"
]
},
{
"index": "idx_loop_prod_timestamps",
"usable": true,
"key_parts": [
"partner_id",
"type_id",
"created",
"deleted",
"updated",
"post_id"
]
},
{
"index": "idx_loop_prod",
"usable": true,
"key_parts": [
"created",
"partner_id",
"type_id",
"market_id",
"user_id",
"deleted",
"post_id"
]
},
{
"index": "idx_loop_prod_type_id_partner_id_created_deleted",
"usable": true,
"key_parts": [
"type_id",
"partner_id",
"created",
"deleted",
"post_id"
]
}
],
"best_covering_index_scan": {
"index": "idx_loop_prod_type_id_partner_id_created_deleted",
"cost": 8.91e6,
"chosen": true
},
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_loop_prod_timestamps",
"tree_travel_cost": 1.25,
"num_groups": 7046448,
"rows": 17933224,
"cost": 2.47e7
},
{
"index": "idx_loop_prod",
"tree_travel_cost": 1.25,
"num_groups": 6972159,
"rows": 25495540,
"cost": 2.98e7
},
{
"index": "idx_loop_prod_type_id_partner_id_created_deleted",
"tree_travel_cost": 1.25,
"num_groups": 7040252,
"rows": 17309876,
"cost": 2.45e7
}
]
},
"best_skip_scan_summary": {
"type": "skip_scan",
"index": "idx_loop_prod_type_id_partner_id_created_deleted",
"key_parts_used_for_access": [
"type_id",
"partner_id",
"created",
"deleted"
],
"prefix ranges": [
"PostType-1cdac073-9cfd-4388-98ca-86b140921adb <= type_id <= PostType-1cdac073-9cfd-4388-98ca-86b140921adb AND VMF <= partner_id <= VMF",
"PostType-275e2809-5658-48ba-b3cf-0884f5851347 <= type_id <= PostType-275e2809-5658-48ba-b3cf-0884f5851347 AND VMF <= partner_id <= VMF",
"PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 <= type_id <= PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 AND VMF <= partner_id <= VMF",
"PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 <= type_id <= PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 AND VMF <= partner_id <= VMF",
"PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba <= type_id <= PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba AND VMF <= partner_id <= VMF",
"PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 <= type_id <= PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 AND VMF <= partner_id <= VMF"
],
"range": [
"NULL <= deleted <= NULL"
],
"chosen": false,
"cause": "cost"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"PostType-1cdac073-9cfd-4388-98ca-86b140921adb <= type_id <= PostType-1cdac073-9cfd-4388-98ca-86b140921adb",
"PostType-275e2809-5658-48ba-b3cf-0884f5851347 <= type_id <= PostType-275e2809-5658-48ba-b3cf-0884f5851347",
"PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 <= type_id <= PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9",
"PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 <= type_id <= PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9",
"PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba <= type_id <= PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba",
"PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 <= type_id <= PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 23325020,
"cost": 2.35e6,
"chosen": true
},
{
"index": "idx_loop_prod_timestamps",
"ranges": [
"VMF <= partner_id <= VMF AND PostType-1cdac073-9cfd-4388-98ca-86b140921adb <= type_id <= PostType-1cdac073-9cfd-4388-98ca-86b140921adb",
"VMF <= partner_id <= VMF AND PostType-275e2809-5658-48ba-b3cf-0884f5851347 <= type_id <= PostType-275e2809-5658-48ba-b3cf-0884f5851347",
"VMF <= partner_id <= VMF AND PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 <= type_id <= PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9",
"VMF <= partner_id <= VMF AND PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 <= type_id <= PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9",
"VMF <= partner_id <= VMF AND PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba <= type_id <= PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba",
"VMF <= partner_id <= VMF AND PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 <= type_id <= PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": 17933225,
"cost": 6.22e6,
"chosen": false,
"cause": "cost"
},
{
"index": "idx_loop_prod",
"chosen": false,
"cause": "no_valid_range_for_this_index"
},
{
"index": "idx_loop_prod_type_id_partner_id_created_deleted",
"ranges": [
"PostType-1cdac073-9cfd-4388-98ca-86b140921adb <= type_id <= PostType-1cdac073-9cfd-4388-98ca-86b140921adb AND VMF <= partner_id <= VMF",
"PostType-275e2809-5658-48ba-b3cf-0884f5851347 <= type_id <= PostType-275e2809-5658-48ba-b3cf-0884f5851347 AND VMF <= partner_id <= VMF",
"PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 <= type_id <= PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 AND VMF <= partner_id <= VMF",
"PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 <= type_id <= PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 AND VMF <= partner_id <= VMF",
"PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba <= type_id <= PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba AND VMF <= partner_id <= VMF",
"PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 <= type_id <= PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 AND VMF <= partner_id <= VMF"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": 17309875,
"cost": 6.05e6,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 23325020,
"ranges": [
"PostType-1cdac073-9cfd-4388-98ca-86b140921adb <= type_id <= PostType-1cdac073-9cfd-4388-98ca-86b140921adb",
"PostType-275e2809-5658-48ba-b3cf-0884f5851347 <= type_id <= PostType-275e2809-5658-48ba-b3cf-0884f5851347",
"PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 <= type_id <= PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9",
"PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 <= type_id <= PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9",
"PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba <= type_id <= PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba",
"PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 <= type_id <= PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301"
]
},
"rows_for_plan": 23325020,
"cost_for_plan": 2.35e6,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`loop`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_loop_prod_timestamps",
"chosen": false,
"cause": "range_uses_more_keyparts"
},
{
"rows_to_scan": 23325020,
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
},
"resulting_rows": 2.33e7,
"cost": 4.68e6,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 2.33e7,
"cost_for_plan": 4.68e6,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null))",
"attached_conditions_computation": [
{
"table": "`loop`",
"rechecking_index_usage": {
"recheck_reason": "low_limit",
"limit": 1000,
"row_estimate": 2.33e7
}
}
],
"attached_conditions_summary": [
{
"table": "`loop`",
"attached": "((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null))"
}
]
}
},
{
"optimizing_distinct_group_by_order_by": {
}
},
{
"finalizing_table_conditions": [
{
"table": "`loop`",
"original_table_condition": "((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null))",
"final_table_condition ": "((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null))"
}
]
},
{
"refine_plan": [
{
"table": "`loop`"
}
]
},
{
"considering_tmp_tables": [
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
},
0,
0
1 Answer 1
This bug "SELECT COUNT(*) degraded performance on 8.0 compared to 5.7" when json columns exist is reported to have been fixed in mysql 8.0.35
answered Dec 11, 2024 at 19:51
lang-sql