Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17752

Plan changes from hash_index_merge to index_merge with new optimizer defaults

    Details

      Description

      The mtr test case

      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;
      

      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	index_merge	City_Population,City_Name	City_Population,City_Name	4,35	NULL	96	Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNL join)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                varun Varun Gupta
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: