• Monty's avatar
    Added EQ_REF chaining to the greedy_optimizer · 515b9ad0
    Monty authored
    MDEV-28073 Slow query performance in MariaDB when using many table
    
    The idea is to prefer and chain EQ_REF tables (tables that uses an
    unique key to find a row) when searching for the best table combination.
    This significantly reduces row combinations that has to be examined.
    This is optimization is enabled when setting optimizer_prune_level=2
    (which is now default).
    
    Implementation:
    - optimizer_prune_level has a new level, 2, which enables EQ_REF
      optimization in addition to the pruning done by level 1.
      Level 2 is now default.
    - Added JOIN::eq_ref_tables that contains bits of tables that could use
      potentially use EQ_REF access in the query.  This is calculated
      in sort_and_filter_keyuse()
    
    Under optimizer_prune_level=2:
    - When the greedy_optimizer notices that the preceding table was an
      EQ_REF table, it tries to add an EQ_REF table next. If an EQ_REF
      table exists, only this one will be considered at this level.
      We also collect all EQ_REF tables chained by the next levels and these
      are ignored on the starting level as we have already examined these.
      If no EQ_REF table exists, we continue as normal.
    
    This optimization speeds up the greedy_optimizer combination test with
    ~25%
    
    Other things:
    - I ported the changes in MySQL 5.7 to greedy_optimizer.test to MariaDB
      to be able to ensure we can handle all cases that MySQL can do.
    - I have run all tests with --mysqld=--optimizer_prune_level=1 to verify that
      there where no test changes.
    515b9ad0
opt_split.cc 43.8 KB