revision-id: f0db071ffeb1a6f3b8e048932c305f2c3e4a28bd (mariadb-10.0.36-80-gf0db071ffeb) parent(s): a84d87fde8c0bc325c8e00f06ea02bcd84a75d55 author: Varun Gupta committer: Varun Gupta timestamp: 2018-11-17 03:25:27 +0530 message: MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults The code in best_access_path function, when it does not find a key suitable for ref access and join_cache_level is set to a value so that hash_join is possible we build a hash key. Later in the function we compare the cost of ref access with table scan (or index scan or quick selects). No need to do this when we have got the hash key. --- mysql-test/r/mdev-17752.result | 68 ++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/mdev-17752.test | 58 +++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 4 ++- 3 files changed, 129 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/mdev-17752.result b/mysql-test/r/mdev-17752.result new file mode 100644 index 00000000000..cc4a75e9d61 --- /dev/null +++ b/mysql-test/r/mdev-17752.result @@ -0,0 +1,68 @@ +# Added a new test to help with the review, would not be easy get a small testcase +# this one, for now just picked it from the test main.join_cache +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='optimize_join_buffer_size=on'; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set @@optimizer_switch='semijoin_with_cache=on'; +set @@optimizer_switch='outer_join_with_cache=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=4; +set @local_join_cache_test_optimizer_switch_default=@@optimizer_switch; +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL +); +CREATE TABLE City ( +ID int(11) NOT NULL, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0' +); +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0' +); +SELECT COUNT(*) FROM Country; +COUNT(*) +239 +SELECT COUNT(*) FROM City; +COUNT(*) +4079 +SELECT COUNT(*) FROM CountryLanguage; +COUNT(*) +984 +show variables like 'join_buffer_size'; +Variable_name Value +join_buffer_size 131072 +set join_cache_level=4; +CREATE INDEX City_Population ON City(Population); +CREATE INDEX City_Name ON City(Name); +ANALYZE TABLE City; +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND City.Population > 5000000 +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where +1 SIMPLE City hash_range City_Population #hash#$hj:City_Population 3:4 world.Country.Code 25 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND +(City.Population > 5000000 OR City.Name LIKE 'Za%') +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where +1 SIMPLE City hash_index_merge City_Population,City_Name #hash#$hj:City_Population,City_Name 3:4,35 world.Country.Code 96 Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNLH join) +DROP INDEX City_Population ON City; +DROP INDEX City_Name ON City; +DROP DATABASE world; diff --git a/mysql-test/t/mdev-17752.test b/mysql-test/t/mdev-17752.test new file mode 100644 index 00000000000..f1afdaec56a --- /dev/null +++ b/mysql-test/t/mdev-17752.test @@ -0,0 +1,58 @@ +--echo # Added a new test to help with the review, would not be easy get a small testcase +--echo # this one, for now just picked it from the test main.join_cache + +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='optimize_join_buffer_size=on'; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set @@optimizer_switch='semijoin_with_cache=on'; +set @@optimizer_switch='outer_join_with_cache=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=4; +set @local_join_cache_test_optimizer_switch_default=@@optimizer_switch; +set names utf8; + +CREATE DATABASE world; + +use world; + +--source include/world_schema1.inc + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +SELECT COUNT(*) FROM Country; +SELECT COUNT(*) FROM City; +SELECT COUNT(*) FROM CountryLanguage; + +show variables like 'join_buffer_size'; + +set join_cache_level=4; + +CREATE INDEX City_Population ON City(Population); +CREATE INDEX City_Name ON City(Name); + +--disable_result_log +ANALYZE TABLE City; +--enable_result_log + +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population + FROM Country LEFT JOIN City + ON City.Country=Country.Code AND City.Population > 5000000 + WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; + +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population + FROM Country LEFT JOIN City + ON City.Country=Country.Code AND + (City.Population > 5000000 OR City.Name LIKE 'Za%') + WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +DROP INDEX City_Population ON City; +DROP INDEX City_Name ON City; +DROP DATABASE world; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ca9a6a46fda..36f14b9b5eb 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6231,7 +6231,8 @@ best_access_path(JOIN *join, 'range' access using index IDX, and the best way to perform 'ref' access is to use the same index IDX, with the same or more key parts. (note: it is not clear how this rule is/should be extended to - index_merge quick selects) + index_merge quick selects). Also if we have a hash join we prefer that + over a table scan. (3) See above note about InnoDB. (4) NOT ("FORCE INDEX(...)" is used for table and there is 'ref' access path, but there is no quick select) @@ -6247,6 +6248,7 @@ best_access_path(JOIN *join, be used for cases with small datasets, which is annoying. */ if ((records >= s->found_records || best > s->read_time) && // (1) + !(best_key && best_key->key == MAX_KEY) && // (2) !(s->quick && best_key && s->quick->index == best_key->key && // (2) best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2) !((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3)