I am currently testing the upgrade from MariaDB 10.3 to 10.4, and I couldn't understand how come my application would simply not work, putting MariaDB using 100% CPU and never coming back with the results of the complex queries that contain several JOINs and subqueries.
After running EXPLAIN, I see that the "FORCE INDEX" is simply being ignored, causing the complex query to do a FULL TABLE SCAN, which completely kills my application. Removing FORCE INDEX causes the plan to be bad anyway.
After reading this thread, I see the cause is the new default of "optimizer_use_condition_selectivity"
https://community.centminmod.com/threads/anyone-using-mariadb-10-4-8-on-live-and-busy-site.18327/page-3
When I change optimizer_use_condition_selectivity = 1, my application works well and fast again.
I think we hit this one almost immediately. Upgraded one replica from 10.3 to 10.5 and had to quickly pull it out of the read pool due to queries taking so long.
This config change isn't documented anywhere on the 10.3 to 10.4 upgrade page. It would be helpful to have this documented there as well as a link to this ticket. I had to dig to find this.
Here is one example query:
SELECT bbs_visitors.user_id, bbs_visitors.forum_id, bbs_visitors.visitdate, users.user_id, users.user_name, users.password, users.first_name, users.last_name, users.last_modified, users.origin, users.email, users.last_pw_send, users.has_profile, users.validated, users.in_fannet, users.locked, users.update_version, users.last_pw_change, users.last_email_confirmation FROM bbs_visitors JOIN users ON bbs_visitors.user_id=users.user_id LEFT JOIN user_options ON user_options.user_id = users.user_id and user_options.option_id = 27 WHERE bbs_visitors.user_id IN ('8791150', '3009427') AND visitdate >= DATE_SUB(NOW(), INTERVAL 20 MINUTE) AND (user_options.user_id IS NULL OR user_options.value = '0')
|
Table definitions:
Create Table: CREATE TABLE `bbs_visitors` (
|
`user_id` int(11) NOT NULL DEFAULT 0,
|
`forum_id` int(11) NOT NULL DEFAULT 0,
|
`visitdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
PRIMARY KEY (`user_id`),
|
KEY `visitdate` (`visitdate`),
|
KEY `forum_id` (`forum_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=1
|
|
|
Create Table: CREATE TABLE `user_options` (
|
`option_id` int(11) NOT NULL DEFAULT 0,
|
`user_id` int(11) NOT NULL DEFAULT 0,
|
`value` text DEFAULT NULL,
|
PRIMARY KEY (`option_id`,`user_id`),
|
KEY `user_options_0` (`user_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
|
|
Create Table: CREATE TABLE `users` (
|
`user_id` int(11) NOT NULL AUTO_INCREMENT,
|
`user_name` varchar(20) NOT NULL DEFAULT '',
|
`password` varchar(72) DEFAULT NULL,
|
`first_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
`last_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
`last_modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
`origin` datetime DEFAULT NULL,
|
`email` varchar(50) NOT NULL DEFAULT '',
|
`receive_update` char(1) DEFAULT NULL,
|
`last_pw_send` datetime DEFAULT NULL,
|
`website_url` varchar(60) DEFAULT NULL,
|
`website_title` varchar(30) DEFAULT NULL,
|
`receive_portal_daily` char(1) DEFAULT 'Y',
|
`has_profile` char(1) DEFAULT 'N',
|
`validated` char(1) NOT NULL DEFAULT '',
|
`in_fannet` char(1) DEFAULT 'N',
|
`aimname` varchar(30) NOT NULL DEFAULT '',
|
`webcam_url` varchar(80) DEFAULT NULL,
|
`locked` tinyint(4) NOT NULL DEFAULT 0,
|
`update_version` int(6) DEFAULT NULL,
|
`last_pw_change` datetime NOT NULL,
|
`last_email_confirmation` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
PRIMARY KEY (`user_id`),
|
UNIQUE KEY `user_name_2` (`user_name`),
|
KEY `password` (`password`),
|
KEY `email` (`email`),
|
KEY `has_profile` (`has_profile`),
|
KEY `validated` (`validated`),
|
KEY `first_name` (`first_name`),
|
KEY `last_name` (`last_name`),
|
KEY `origin` (`origin`),
|
KEY `update_version` (`update_version`),
|
KEY `last_pw_change` (`last_pw_change`),
|
KEY `last_email_confirmation` (`last_email_confirmation`)
|
) ENGINE=InnoDB AUTO_INCREMENT=9218654 DEFAULT CHARSET=utf8 PACK_KEYS=1
|
Trace:
analyze format=json SELECT bbs_visitors.user_id, bbs_visitors.forum_id, bbs_visitors.visitdate, users.user_id, users.user_name, users.password, users.first_name, users.last_name, users.last_modified, users.origin, users.email, users.last_pw_send, users.has_profile, users.validated, users.in_fannet, users.locked, users.update_version, users.last_pw_change, users.last_email_confirmation FROM bbs_visitors JOIN users ON bbs_visitors.user_id=users.user_id LEFT JOIN user_options ON user_options.user_id = users.user_id and user_options.option_id = 27 WHERE bbs_visitors.user_id IN ('8791150', '3009427') AND visitdate >= DATE_SUB(NOW(), INTERVAL 20 MINUTE) AND (user_options.user_id IS NULL OR user_options.value = '0') {\
|
"steps": [\
|
{\
|
"join_preparation": {\
|
"select_id": 1,\
|
"steps": [\
|
{\
|
"expanded_query": "select bbs_visitors.user_id AS user_id,bbs_visitors.forum_id AS forum_id,bbs_visitors.visitdate AS visitdate,users.user_id AS user_id,users.user_name AS user_name,users.`password` AS `password`,users.first_name AS first_name,users.last_name AS last_name,users.last_modified AS last_modified,users.origin AS origin,users.email AS email,users.last_pw_send AS last_pw_send,users.has_profile AS has_profile,users.validated AS validated,users.in_fannet AS in_fannet,users.locked AS locked,users.update_version AS update_version,users.last_pw_change AS last_pw_change,users.last_email_confirmation AS last_email_confirmation from ((bbs_visitors join users on(bbs_visitors.user_id = users.user_id)) left join user_options on(user_options.user_id = users.user_id and user_options.option_id = 27)) where bbs_visitors.user_id in ('8791150','3009427') and bbs_visitors.visitdate >= current_timestamp() - interval 20 minute and (user_options.user_id is null or user_options.`value` = '0')"\
|
}\
|
]\
|
}\
|
},\
|
{\
|
"join_optimization": {\
|
"select_id": 1,\
|
"steps": [\
|
{\
|
"condition_processing": {\
|
"condition": "WHERE",\
|
"original_condition": "bbs_visitors.user_id in ('8791150','3009427') and bbs_visitors.visitdate >= current_timestamp() - interval 20 minute and (user_options.user_id is null or user_options.`value` = '0') and bbs_visitors.user_id = users.user_id",\
|
"steps": [\
|
{\
|
"transformation": "equality_propagation",\
|
"resulting_condition": "bbs_visitors.user_id in ('8791150','3009427') and bbs_visitors.visitdate >= current_timestamp() - interval 20 minute and (user_options.user_id is null or user_options.`value` = '0') and multiple equal(bbs_visitors.user_id, users.user_id)"\
|
},\
|
{\
|
"transformation": "constant_propagation",\
|
"resulting_condition": "bbs_visitors.user_id in ('8791150','3009427') and bbs_visitors.visitdate >= current_timestamp() - interval 20 minute and (user_options.user_id is null or user_options.`value` = '0') and multiple equal(bbs_visitors.user_id, users.user_id)"\
|
},\
|
{\
|
"transformation": "trivial_condition_removal",\
|
"resulting_condition": "bbs_visitors.user_id in ('8791150','3009427') and bbs_visitors.visitdate >= current_timestamp() - interval 20 minute and (user_options.user_id is null or user_options.`value` = '0') and multiple equal(bbs_visitors.user_id, users.user_id)"\
|
}\
|
]\
|
}\
|
},\
|
{\
|
"table_dependencies": [\
|
{\
|
"table": "bbs_visitors",\
|
"row_may_be_null": false,\
|
"map_bit": 0,\
|
"depends_on_map_bits": []\
|
},\
|
{\
|
"table": "users",\
|
"row_may_be_null": false,\
|
"map_bit": 1,\
|
"depends_on_map_bits": []\
|
},\
|
{\
|
"table": "user_options",\
|
"row_may_be_null": true,\
|
"map_bit": 2,\
|
"depends_on_map_bits": ["1"]\
|
}\
|
]\
|
},\
|
{\
|
"ref_optimizer_key_uses": [\
|
{\
|
"table": "bbs_visitors",\
|
"field": "user_id",\
|
"equals": "users.user_id",\
|
"null_rejecting": false\
|
},\
|
{\
|
"table": "users",\
|
"field": "user_id",\
|
"equals": "bbs_visitors.user_id",\
|
"null_rejecting": false\
|
},\
|
{\
|
"table": "user_options",\
|
"field": "option_id",\
|
"equals": "27",\
|
"null_rejecting": false\
|
},\
|
{\
|
"table": "user_options",\
|
"field": "user_id",\
|
"equals": "bbs_visitors.user_id",\
|
"null_rejecting": false\
|
},\
|
{\
|
"table": "user_options",\
|
"field": "user_id",\
|
"equals": "users.user_id",\
|
"null_rejecting": false\
|
},\
|
{\
|
"table": "user_options",\
|
"field": "user_id",\
|
"equals": "bbs_visitors.user_id",\
|
"null_rejecting": false\
|
},\
|
{\
|
"table": "user_options",\
|
"field": "user_id",\
|
"equals": "users.user_id",\
|
"null_rejecting": false\
|
},\
|
{\
|
"table": "user_options",\
|
"field": "option_id",\
|
"equals": "27",\
|
"null_rejecting": false\
|
}\
|
]\
|
},\
|
{\
|
"eliminated_tables": []\
|
},\
|
{\
|
"rows_estimation": [\
|
{\
|
"table": "bbs_visitors",\
|
"range_analysis": {\
|
"table_scan": {\
|
"rows": 255,\
|
"cost": 54\
|
},\
|
"potential_range_indexes": [\
|
{\
|
"index": "PRIMARY",\
|
"usable": true,\
|
"key_parts": ["user_id"]\
|
},\
|
{\
|
"index": "visitdate",\
|
"usable": true,\
|
"key_parts": ["visitdate", "user_id"]\
|
},\
|
{\
|
"index": "forum_id",\
|
"usable": false,\
|
"cause": "not applicable"\
|
}\
|
],\
|
"setup_range_conditions": [],\
|
"analyzing_range_alternatives": {\
|
"range_scan_alternatives": [\
|
{\
|
"index": "PRIMARY",\
|
"ranges": [\
|
"(3009427) <= (user_id) <= (3009427)",\
|
"(8791150) <= (user_id) <= (8791150)"\
|
],\
|
"rowid_ordered": true,\
|
"using_mrr": false,\
|
"index_only": false,\
|
"rows": 2,\
|
"cost": 2.671892148,\
|
"chosen": true\
|
},\
|
{\
|
"index": "visitdate",\
|
"ranges": [\
|
"(2021-05-07 22:43:53,3009427) <= (visitdate,user_id)"\
|
],\
|
"rowid_ordered": false,\
|
"using_mrr": false,\
|
"index_only": false,\
|
"rows": 96,\
|
"cost": 115.3555462,\
|
"chosen": false,\
|
"cause": "cost"\
|
}\
|
],\
|
"analyzing_roworder_intersect": {\
|
"cause": "too few roworder scans"\
|
},\
|
"analyzing_index_merge_union": []\
|
},\
|
"group_index_range": {\
|
"chosen": false,\
|
"cause": "not single_table"\
|
},\
|
"chosen_range_access_summary": {\
|
"range_access_plan": {\
|
"type": "range_scan",\
|
"index": "PRIMARY",\
|
"rows": 2,\
|
"ranges": [\
|
"(3009427) <= (user_id) <= (3009427)",\
|
"(8791150) <= (user_id) <= (8791150)"\
|
]\
|
},\
|
"rows_for_plan": 2,\
|
"cost_for_plan": 2.671892148,\
|
"chosen": true\
|
}\
|
}\
|
},\
|
{\
|
"table": "bbs_visitors",\
|
"rowid_filters": [\
|
{\
|
"key": "visitdate",\
|
"build_cost": 4.997320495,\
|
"rows": 96\
|
}\
|
]\
|
},\
|
{\
|
"selectivity_for_indexes": [\
|
{\
|
"index_name": "visitdate",\
|
"selectivity_from_index": 0.376470588\
|
}\
|
],\
|
"selectivity_for_columns": [],\
|
"cond_selectivity": 0.376470588\
|
},\
|
{\
|
"table": "users",\
|
"table_scan": {\
|
"rows": 7613212,\
|
"cost": 93055\
|
}\
|
},\
|
{\
|
"table": "user_options",\
|
"range_analysis": {\
|
"table_scan": {\
|
"rows": 4987025,\
|
"cost": 1012753\
|
},\
|
"potential_range_indexes": [\
|
{\
|
"index": "PRIMARY",\
|
"usable": true,\
|
"key_parts": ["option_id", "user_id"]\
|
},\
|
{\
|
"index": "user_options_0",\
|
"usable": false,\
|
"cause": "not applicable"\
|
}\
|
],\
|
"setup_range_conditions": [],\
|
"analyzing_range_alternatives": {\
|
"range_scan_alternatives": [\
|
{\
|
"index": "PRIMARY",\
|
"ranges": ["(27) <= (option_id) <= (27)"],\
|
"rowid_ordered": true,\
|
"using_mrr": false,\
|
"index_only": false,\
|
"rows": 125260,\
|
"cost": 25176.01597,\
|
"chosen": true\
|
}\
|
],\
|
"analyzing_roworder_intersect": {\
|
"cause": "too few roworder scans"\
|
},\
|
"analyzing_index_merge_union": []\
|
},\
|
"group_index_range": {\
|
"chosen": false,\
|
"cause": "not single_table"\
|
},\
|
"chosen_range_access_summary": {\
|
"range_access_plan": {\
|
"type": "range_scan",\
|
"index": "PRIMARY",\
|
"rows": 125260,\
|
"ranges": ["(27) <= (option_id) <= (27)"]\
|
},\
|
"rows_for_plan": 125260,\
|
"cost_for_plan": 25176.01597,\
|
"chosen": true\
|
}\
|
}\
|
},\
|
{\
|
"selectivity_for_indexes": [\
|
{\
|
"index_name": "PRIMARY",\
|
"selectivity_from_index": 0.025117179\
|
}\
|
],\
|
"selectivity_for_columns": [],\
|
"cond_selectivity": 0.025117179\
|
}\
|
]\
|
},\
|
{\
|
"considered_execution_plans": [\
|
{\
|
"plan_prefix": [],\
|
"table": "bbs_visitors",\
|
"best_access_path": {\
|
"considered_access_paths": [\
|
{\
|
"access_type": "range",\
|
"resulting_rows": 96,\
|
"cost": 1.79769e308,\
|
"chosen": true\
|
}\
|
],\
|
"chosen_access_method": {\
|
"type": "range",\
|
"records": 96,\
|
"cost": 1.79769e308,\
|
"uses_join_buffering": false\
|
}\
|
},\
|
"rows_for_plan": 96,\
|
"cost_for_plan": 1.79769e308,\
|
"rest_of_plan": [\
|
{\
|
"plan_prefix": ["bbs_visitors"],\
|
"table": "users",\
|
"best_access_path": {\
|
"considered_access_paths": [\
|
{\
|
"access_type": "eq_ref",\
|
"index": "PRIMARY",\
|
"rows": 1,\
|
"cost": 96,\
|
"chosen": true\
|
},\
|
{\
|
"type": "scan",\
|
"chosen": false,\
|
"cause": "cost"\
|
}\
|
],\
|
"chosen_access_method": {\
|
"type": "eq_ref",\
|
"records": 1,\
|
"cost": 96,\
|
"uses_join_buffering": false\
|
}\
|
},\
|
"rows_for_plan": 96,\
|
"cost_for_plan": 1.79769e308,\
|
"rest_of_plan": [\
|
{\
|
"plan_prefix": ["bbs_visitors", "users"],\
|
"table": "user_options",\
|
"best_access_path": {\
|
"considered_access_paths": [\
|
{\
|
"access_type": "eq_ref",\
|
"index": "PRIMARY",\
|
"rows": 1,\
|
"cost": 96,\
|
"chosen": true\
|
},\
|
{\
|
"access_type": "eq_ref",\
|
"index": "user_options_0",\
|
"rows": 1,\
|
"cost": 96,\
|
"chosen": false,\
|
"cause": "cost"\
|
},\
|
{\
|
"type": "scan",\
|
"chosen": false,\
|
"cause": "cost"\
|
}\
|
],\
|
"chosen_access_method": {\
|
"type": "eq_ref",\
|
"records": 1,\
|
"cost": 96,\
|
"uses_join_buffering": false\
|
}\
|
},\
|
"rows_for_plan": 96,\
|
"cost_for_plan": 1.79769e308,\
|
"estimated_join_cardinality": 96\
|
}\
|
]\
|
}\
|
]\
|
},\
|
{\
|
"plan_prefix": [],\
|
"table": "users",\
|
"best_access_path": {\
|
"considered_access_paths": [\
|
{\
|
"access_type": "scan",\
|
"resulting_rows": 7613212,\
|
"cost": 93055,\
|
"chosen": true\
|
}\
|
],\
|
"chosen_access_method": {\
|
"type": "scan",\
|
"records": 7613212,\
|
"cost": 93055,\
|
"uses_join_buffering": false\
|
}\
|
},\
|
"rows_for_plan": 7613212,\
|
"cost_for_plan": 1615697.4,\
|
"rest_of_plan": [\
|
{\
|
"plan_prefix": ["users"],\
|
"table": "bbs_visitors",\
|
"best_access_path": {\
|
"considered_access_paths": [\
|
{\
|
"access_type": "eq_ref",\
|
"index": "PRIMARY",\
|
"rows": 1,\
|
"cost": 7613212,\
|
"chosen": true\
|
},\
|
{\
|
"type": "scan",\
|
"chosen": false,\
|
"cause": "cost"\
|
}\
|
],\
|
"chosen_access_method": {\
|
"type": "eq_ref",\
|
"records": 1,\
|
"cost": 7613212,\
|
"uses_join_buffering": false\
|
}\
|
},\
|
"rows_for_plan": 7613212,\
|
"cost_for_plan": 10751551.8,\
|
"selectivity": 0.376470588,\
|
"rest_of_plan": [\
|
{\
|
"plan_prefix": ["users", "bbs_visitors"],\
|
"table": "user_options",\
|
"best_access_path": {\
|
"considered_access_paths": [\
|
{\
|
"access_type": "eq_ref",\
|
"index": "PRIMARY",\
|
"rows": 1,\
|
"cost": 2866150.4,\
|
"chosen": true\
|
},\
|
{\
|
"access_type": "eq_ref",\
|
"index": "user_options_0",\
|
"rows": 1,\
|
"cost": 2866150.4,\
|
"chosen": false,\
|
"cause": "cost"\
|
},\
|
{\
|
"type": "scan",\
|
"chosen": false,\
|
"cause": "cost"\
|
}\
|
],\
|
"chosen_access_method": {\
|
"type": "eq_ref",\
|
"records": 1,\
|
"cost": 2866150.4,\
|
"uses_join_buffering": false\
|
}\
|
},\
|
"rows_for_plan": 2866150.4,\
|
"cost_for_plan": 14190932.28,\
|
"estimated_join_cardinality": 2866150.4\
|
}\
|
]\
|
},\
|
{\
|
"plan_prefix": ["users"],\
|
"table": "user_options",\
|
"best_access_path": {\
|
"considered_access_paths": [\
|
{\
|
"access_type": "eq_ref",\
|
"index": "PRIMARY",\
|
"rows": 1,\
|
"cost": 7613212,\
|
"chosen": true\
|
},\
|
{\
|
"access_type": "eq_ref",\
|
"index": "user_options_0",\
|
"rows": 1,\
|
"cost": 7613212,\
|
"chosen": false,\
|
"cause": "cost"\
|
},\
|
{\
|
"type": "scan",\
|
"chosen": false,\
|
"cause": "cost"\
|
}\
|
],\
|
"chosen_access_method": {\
|
"type": "eq_ref",\
|
"records": 1,\
|
"cost": 7613212,\
|
"uses_join_buffering": false\
|
}\
|
},\
|
"rows_for_plan": 7613212,\
|
"cost_for_plan": 10751551.8,\
|
"pruned_by_heuristic": true\
|
}\
|
]\
|
}\
|
]\
|
},\
|
{\
|
"best_join_order": ["users", "bbs_visitors", "user_options"]\
|
},\
|
{\
|
"attaching_conditions_to_tables": {\
|
"original_condition": "bbs_visitors.user_id = users.user_id and bbs_visitors.user_id in ('8791150','3009427') and bbs_visitors.visitdate >= <cache>(current_timestamp() - interval 20 minute) and (user_options.user_id is null or user_options.`value` = '0')",\
|
"attached_conditions_computation": [],\
|
"attached_conditions_summary": [\
|
{\
|
"table": "users",\
|
"attached": null\
|
},\
|
{\
|
"table": "bbs_visitors",\
|
"attached": "bbs_visitors.user_id in ('8791150','3009427') and bbs_visitors.visitdate >= <cache>(current_timestamp() - interval 20 minute)"\
|
},\
|
{\
|
"table": "user_options",\
|
"attached": "trigcond(user_options.user_id is null or user_options.`value` = '0')"\
|
}\
|
]\
|
}\
|
}\
|
]\
|
}\
|
},\
|
{\
|
"join_execution": {\
|
"select_id": 1,\
|
"steps": []\
|
}\
|
}\
|
]\
|
} 0 0
|
Luckily setting optimizer_use_condition_selectivity back to 1 immediately fixed this.
Not reproducible using DDL provided by medletan with fake data inserted.
If it makes any difference, the users table has about 8.5 million rows and the user_options table has 7.2 million rows (just 100 rows in bbs_visitors).
Tested this again by changing optimizer_use_condition_selectivity back to 4. I'm seeing some queries in the slow log but it's not as bad as it was when I first posted. The example query is still taking about 6-8 seconds where I think it was taking 30+ before. Not seeing these queries in the slow log on any other replica so I think optimizer_use_condition_selectivity being set to 4 is still slowing this query down (maybe others).
Nothing else has changed on these servers since I first posted.
Loaded a dataset (Approx 3.5Gb, 13 million records) into tmpfs (110Gb RAM free) instance, and confirmed that query execution times (in tmpfs) are:
With session optimizer_use_condition_selectivity:
|
4: first exec: 12.7 sec, second exec: 12.4 sec (default)
|
3: first exec: 12.5 sec, second exec: 12.5 sec
|
2: first exec: 12.5 sec, second exec: 12.5 sec
|
1: first exec: 0.077 sec, second exec: 0.039 sec
|
Query produces empty result in all instances.
|
optimizer_use_condition_selectivity=1 vs optimizer_use_condition_selectivity=4 issue confirmed on these versions/revisions:
10.4.22 a75813d46739f1e3aea9821b163c0ff1332a017e (Optimized)
|
10.5.13 4eb7217ec33fef8d23f2dda0c97b442508c81b1d (Optimized)
|
10.6.5 ebd52051206620a0944ffc084582532c0c394523 (Optimized)
|
10.7.1 b4911f5a34f8dcfb642c6f14535bc9d5d97ade44 (Optimized)
|
10.2 (different default) and 10.3 
give different results. 10.2 is expected, 10.3 is not:
|
10.2.41 Build 13-Oct-2021 (Optimized) |
With session optimizer_use_condition_selectivity:
|
4: first exec: 15.5 sec, second exec: 15.4 sec
|
3: first exec: 15.5 sec, second exec: 15.4 sec
|
2: first exec: 15.7 sec, second exec: 15.4 sec
|
1: first exec: 0.07 sec, second exec: 0.04 sec (default)
|
|
10.3.32 00affc324cd69f5e00befe195de930c4df7b2f32 (Optimized) |
4: first exec: 0.047 sec, second exec: 0.036 sec
|
3: first exec: 0.053 sec, second exec: 0.052 sec
|
2: first exec: 0.046 sec, second exec: 0.044 sec
|
1: first exec: 0.047 sec, second exec: 0.036 sec (default)
|
In summary, 10.2 is affected but only if the default is changed to =4, and more interestingly, the bug does not exist in 10.3.32 at revision 00affc324cd69f5e00befe195de930c4df7b2f32, build 13-Oct-21. Double confirmed with a re-load of the full dataset in 10.3 rather than data dir copy.
It may thus be possible to look at 10.3 code vs others and fix based on diffs.
Could you please provide the SHOW EXPLAIN for a good and bad query in 10.3
Anyway, there is several issues with selectivity calculation in MariaDB. That is what I have been working on the last 3 months, should hopefully be done in 10.10. Until then, I don't recommend to use selectivity=4 as a general option, only for specific tested queries.
I will ask for the data set and try it against my new tree!
00affc324cd69f5e00befe195de930c4df7b2f32 is a null merge
Can you find the commit that caused the issue to happen? Elena should have some tools for that...
For the 2 query, can you please post the following variables used:
use_stat_tables
in_predicate_conversion_threshold
optimizer_switch
optimizer_use_condition_selectivity
join_cache_level
Where analyze table run on the involved tables?
I have run "query 2" and the iwork query on the 10.7-selecitivity branch, without and without "analyze table", and all queries was < 1 second independent on the setting of optimizer_use_condition_selectivity
The explain I get for the iwork query with optimizer_use_condition_selectivity=4 is:
-------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | <subquery6> | ALL | distinct_key | NULL | NULL | NULL | 1256 | |
| 1 | PRIMARY | node | eq_ref | PRIMARY,NODE_COMPOSITE_IDX,NODE_IDX02 | PRIMARY | 4 | dbs.node_link.CHILD_NODENBR | 1 | Using where |
| 1 | PRIMARY | <subquery8> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | Using where |
| 1 | PRIMARY | <subquery4> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | Using where |
| 1 | PRIMARY | node | ref | PRIMARY,NODE_COMPOSITE_IDX,NODE_IDX01,NODE_IDX03 | NODE_IDX03 | 3 | const | 166 | Using where |
| 1 | PRIMARY | node_link | ref | NODE_LINK_IDX,NODE_LINK_IDX01,NODE_LINK_IDX02,NODE_LINK_IDX03 | NODE_LINK_IDX03 | 9 | dbs.node.NODENBR,const | 41 | Using index condition; Using where; FirstMatch((sj-nest)) |
| 6 | MATERIALIZED | node | ref | PRIMARY,NODE_COMPOSITE_IDX,NODE_IDX01,NODE_IDX03 | NODE_IDX03 | 3 | const | 30 | Using where |
| 6 | MATERIALIZED | node_link | ref | NODE_LINK_IDX,NODE_LINK_IDX01,NODE_LINK_IDX02,NODE_LINK_IDX03 | NODE_LINK_IDX03 | 9 | dbs.node.NODENBR,const | 41 | Using index condition; Using where |
| 8 | MATERIALIZED | node | ref | PRIMARY,NODE_COMPOSITE_IDX,NODE_IDX01,NODE_IDX03 | NODE_IDX01 | 153 | const | 446 | Using where; Using index |
| 8 | MATERIALIZED | node_link | ref | NODE_LINK_IDX,NODE_LINK_IDX01,NODE_LINK_IDX02,NODE_LINK_IDX03 | NODE_LINK_IDX03 | 9 | dbs.node.NODENBR,const | 41 | Using index condition |
| 4 | MATERIALIZED | node | ref | PRIMARY,NODE_COMPOSITE_IDX,NODE_IDX01,NODE_IDX03 | NODE_IDX03 | 3 | const | 6 | Using where |
| 4 | MATERIALIZED | node_link | ref | NODE_LINK_IDX,NODE_LINK_IDX01,NODE_LINK_IDX02,NODE_LINK_IDX03 | NODE_LINK_IDX03 | 9 | dbs.node.NODENBR,const | 41 | Using index condition |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Using selectivity > 1 on any version before 11.0 is not advisable. If it works, then things are good. If not, better to not use it and instead test to see if 11.0 fixes the issue.
There are several problems with the selectivity code before 11.0 and there is not any 'easy fix' that can be done. It's not advisable to do a big fix of 100-1000 lines of code in an otherwise stable release as there
is a big change to get notable regressions if trying to do that.
"Using selectivity > 1 on any version before 11.0 is not advisable. " <-- Is this documented?
If not, then this should be documented before this bug is closed.
This is not a bug, but a reflection of the state of MariaDB before 11.0