commit 61a6413b32d26148d24c3bcda31659cc5969e030 Author: Jeremy Cole Date: Wed Sep 25 00:11:44 2019 +0000 Add reconsider_index_for_order optimizer_switch diff --git a/mysql-test/r/reconsider_index_for_order.result b/mysql-test/r/reconsider_index_for_order.result new file mode 100644 index 00000000000..e6ef641547f --- /dev/null +++ b/mysql-test/r/reconsider_index_for_order.result @@ -0,0 +1,55 @@ +CREATE TABLE `t` ( +`id` BIGINT NOT NULL, +`other_id` BIGINT NOT NULL, +`covered_column` VARCHAR(50) NOT NULL, +`non_covered_column` VARCHAR(50) NOT NULL, +PRIMARY KEY (`id`), +INDEX `index_other_id_covered_column` (`other_id`, `covered_column`) +); +INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES (10, 10 % 1000, '10', '10'); +INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES (9, 9 % 1000, '9', '9'); +INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES (8, 8 % 1000, '8', '8'); +INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES (7, 7 % 1000, '7', '7'); +INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES (6, 6 % 1000, '6', '6'); +INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES (5, 5 % 1000, '5', '5'); +INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES (4, 4 % 1000, '4', '4'); +INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES (3, 3 % 1000, '3', '3'); +INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES (2, 2 % 1000, '2', '2'); +INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES (1, 1 % 1000, '1', '1'); +SET +optimizer_trace = "enabled=on", +optimizer_trace_max_mem_size = 1000000, +end_markers_in_json = ON; +SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 2; +non_covered_column +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_1 FROM information_schema.optimizer_trace; +should_be_1 +1 +SELECT * FROM `t` WHERE `other_id` = 555 AND `id` IN (1, 2, 3) ORDER BY `id` DESC; +id other_id covered_column non_covered_column +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_1 FROM information_schema.optimizer_trace; +should_be_1 +1 +SET optimizer_switch = "reconsider_index_for_order=off"; +SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 2; +non_covered_column +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_0 FROM information_schema.optimizer_trace; +should_be_0 +0 +SELECT * FROM `t` WHERE `other_id` = 555 AND `id` IN (1, 2, 3) ORDER BY `id` DESC; +id other_id covered_column non_covered_column +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_0 FROM information_schema.optimizer_trace; +should_be_0 +0 +SET optimizer_switch = default; +SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 2; +non_covered_column +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_1 FROM information_schema.optimizer_trace; +should_be_1 +1 +SELECT * FROM `t` WHERE `other_id` = 555 AND `id` IN (1, 2, 3) ORDER BY `id` DESC; +id other_id covered_column non_covered_column +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_1 FROM information_schema.optimizer_trace; +should_be_1 +1 +DROP TABLE t; diff --git a/mysql-test/t/reconsider_index_for_order.test b/mysql-test/t/reconsider_index_for_order.test new file mode 100644 index 00000000000..70e53f33269 --- /dev/null +++ b/mysql-test/t/reconsider_index_for_order.test @@ -0,0 +1,53 @@ +# Features of the test table: +# * Synthetic primary key (`id`). +# * Secondary key. +# * Index with a covered and non-covered column. + +CREATE TABLE `t` ( + `id` BIGINT NOT NULL, + `other_id` BIGINT NOT NULL, + `covered_column` VARCHAR(50) NOT NULL, + `non_covered_column` VARCHAR(50) NOT NULL, + PRIMARY KEY (`id`), + INDEX `index_other_id_covered_column` (`other_id`, `covered_column`) +); + +# The size of the table actually doesn't matter to test whether the +# optimizer switch works, but larger tables are required to show the +# bad performance of a bad optimizer choice. + +let $n = 10; +while ($n) +{ + eval INSERT INTO t (id, other_id, covered_column, non_covered_column) VALUES ($n, $n % 1000, '$n', '$n'); + dec $n; +} + +SET + optimizer_trace = "enabled=on", + optimizer_trace_max_mem_size = 1000000, + end_markers_in_json = ON; + +SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 2; +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_1 FROM information_schema.optimizer_trace; + +SELECT * FROM `t` WHERE `other_id` = 555 AND `id` IN (1, 2, 3) ORDER BY `id` DESC; +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_1 FROM information_schema.optimizer_trace; + +SET optimizer_switch = "reconsider_index_for_order=off"; + +SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 2; +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_0 FROM information_schema.optimizer_trace; + +SELECT * FROM `t` WHERE `other_id` = 555 AND `id` IN (1, 2, 3) ORDER BY `id` DESC; +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_0 FROM information_schema.optimizer_trace; + +SET optimizer_switch = default; + +SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 2; +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_1 FROM information_schema.optimizer_trace; + +SELECT * FROM `t` WHERE `other_id` = 555 AND `id` IN (1, 2, 3) ORDER BY `id` DESC; +SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%') AS should_be_1 FROM information_schema.optimizer_trace; + +DROP TABLE t; \ No newline at end of file diff --git a/sql/sql_const.h b/sql/sql_const.h index f98302cbd4a..e5ed9f56d71 100644 --- a/sql/sql_const.h +++ b/sql/sql_const.h @@ -301,7 +301,8 @@ #define OPTIMIZER_SWITCH_USE_INDEX_EXTENSIONS (1ULL << 16) #define OPTIMIZER_SWITCH_COND_FANOUT_FILTER (1ULL << 17) #define OPTIMIZER_SWITCH_DERIVED_MERGE (1ULL << 18) -#define OPTIMIZER_SWITCH_LAST (1ULL << 19) +#define OPTIMIZER_SWITCH_RECONSIDER_INDEX_FOR_ORDER (1ULL << 19) +#define OPTIMIZER_SWITCH_LAST (1ULL << 20) #define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ @@ -320,7 +321,8 @@ OPTIMIZER_SWITCH_SUBQ_MAT_COST_BASED | \ OPTIMIZER_SWITCH_USE_INDEX_EXTENSIONS | \ OPTIMIZER_SWITCH_COND_FANOUT_FILTER | \ - OPTIMIZER_SWITCH_DERIVED_MERGE) + OPTIMIZER_SWITCH_DERIVED_MERGE | \ + OPTIMIZER_SWITCH_RECONSIDER_INDEX_FOR_ORDER) enum SHOW_COMP_OPTION { SHOW_OPTION_YES, SHOW_OPTION_NO, SHOW_OPTION_DISABLED}; diff --git a/sql/sql_optimizer.cc b/sql/sql_optimizer.cc index 83952346864..b14a386c368 100644 --- a/sql/sql_optimizer.cc +++ b/sql/sql_optimizer.cc @@ -1381,7 +1381,8 @@ void JOIN::test_skip_sort() } } } - else if (order && // ORDER BY wo/ preceding GROUP BY + else if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_RECONSIDER_INDEX_FOR_ORDER) && + order && // ORDER BY wo/ preceding GROUP BY (simple_order || skip_sort_order)) // which is possibly skippable { if (test_if_skip_sort_order(tab, order, m_select_limit, false, diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index a998560be06..95348bc9a7a 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2942,6 +2942,7 @@ static const char *optimizer_switch_names[]= "materialization", "semijoin", "loosescan", "firstmatch", "duplicateweedout", "subquery_materialization_cost_based", "use_index_extensions", "condition_fanout_filter", "derived_merge", + "reconsider_index_for_order", "default", NullS }; static Sys_var_flagset Sys_optimizer_switch(