[Commits] a4068115349: MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults

Varun varunraiko1803 at gmail.com
Mon May 20 15:15:27 EEST 2019


revision-id: a4068115349c215a5bfee5b47dd091e386993e55 (mariadb-10.1.39-38-ga4068115349)
parent(s): 91efcc6392cef920aa3697dc9789830ae9cdd379
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-05-20 17:44:55 +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/join_cache.result | 31 +++++++++++++++++++++++++++++++
 mysql-test/t/join_cache.test   | 34 ++++++++++++++++++++++++++++++++++
 sql/sql_select.cc              |  4 +++-
 3 files changed, 68 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 01339f7c191..cf23979b49a 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -2885,6 +2885,37 @@ Klaipeda	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 Panevezys	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 set join_cache_level=default;
 set join_buffer_size=default;
+#
+# MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults
+#
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_use_stat_tables=@@use_stat_tables;
+set optimizer_use_condition_selectivity=4;
+set use_stat_tables='preferably';
+use world;
+set join_cache_level=4;
+CREATE INDEX City_Name ON City(Name);
+ANALYZE TABLE City, Country;
+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	range	Name	Name	302	NULL	15	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	City	hash_range	Population,Country	#hash#Country: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	range	Name	Name	302	NULL	15	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	City	hash_index_merge	Population,Country,City_Name	#hash#Country:Population,City_Name	3:4,35	world.Country.Code	96	Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join)
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set @@use_stat_tables=@save_use_stat_tables;
+set join_cache_level=default;
 DROP DATABASE world;
 use test;
 CREATE TABLE t1(
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 24dd637052c..48f129c676d 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -967,6 +967,40 @@ SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country
 set join_cache_level=default;
 set join_buffer_size=default;
 
+
+--echo #
+--echo # MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults
+--echo #
+
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_use_stat_tables=@@use_stat_tables;
+set optimizer_use_condition_selectivity=4;
+set use_stat_tables='preferably';
+
+use world;
+set join_cache_level=4;
+CREATE INDEX City_Name ON City(Name);
+
+--disable_result_log
+ANALYZE TABLE City, Country;
+--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;
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set @@use_stat_tables=@save_use_stat_tables;
+set join_cache_level=default;
+
 DROP DATABASE world;
 
 use test;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index c1bec0f1c20..7426540c8d7 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -6396,7 +6396,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)
@@ -6412,6 +6413,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)


More information about the commits mailing list