| Bug #106987 | when set prefer_ordering_index=off, a very simple sql become very slow | ||
|---|---|---|---|
| Submitted: | 12 Apr 2022 7:30 | Modified: | 12 Apr 2022 14:09 |
| Reporter: | WANG GUANGYOU | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[12 Apr 2022 10:39]
WANG GUANGYOU
i think we can decrease the chance by using the optimization when the plan is bad like full table scan or full index scan.
diff --git a/sql/sql_optimizer.cc b/sql/sql_optimizer.cc
index 9b34fab..27ba5f0 100644
--- a/sql/sql_optimizer.cc
+++ b/sql/sql_optimizer.cc
@@ -2117,7 +2117,7 @@ test_if_skip_sort_order(JOIN_TAB *tab, ORDER *order, ha_rows select_limit,
force index for order/group is specified.
*/
if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_PREFER_ORDERING_INDEX) ||
- is_force_index)
+ is_force_index || tab->type() == JT_ALL || tab->type() == JT_INDEX_SCAN)
test_if_cheaper_ordering(tab, order, table, usable_keys,
ref_key_hint,
select_limit,
[12 Apr 2022 14:09]
MySQL Verification Team
Hi Mr. Guangyou, Thank you for your bug report. However, it is not a bug. The optimiser_switch "prefer_ordering_index=on/off" is there to be used and set separately for each query that you have and which is using ORDER BY or GROUP BY or similar ..... Hence, you can not expect to have better results for all types of queries with the same setting of that switch. Not a bug.
Description: After mysql 5.7.33, we can set prefer_ordering_index=off to make some SQL faster. however, some very simple sql become slow. For example, if this sql (no where condition) use primary index, it will be very fast. but it become slow after set prefer_ordering_index=off select * from `t1` order by `id` desc limit 0, 15 explain select * from `t1` order by `id` limit 0, 15\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1416766012 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec) How to repeat: use sysbench to generate a table sbtest1 and pop 5,000,000 rows 1. create table CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `pad` char(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 2. populate data by sysbench 3. run following sbtest@sbtest03:24:51>set session optimizer_switch='prefer_ordering_index=off'; Query OK, 0 rows affected (0.00 sec) sbtest@sbtest03:25:14>explain select * from sbtest1 order by id limit 10; +----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 4804855 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) 4、if disable the optimization set prefer_ordering_index=on it will be ok sbtest@sbtest03:26:41>set session optimizer_switch='prefer_ordering_index=on'; Query OK, 0 rows affected (0.00 sec) sbtest@sbtest03:29:12>explain select * from sbtest1 order by id limit 10; +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 10 | 100.00 | NULL | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec)