join_cache_level:

1 – flat (Block Nested Loop) BNL

2 – incremental BNL  -- *current default*

3 – flat Block Nested Loop Hash (BNLH)

4 – incremental BNLH  -- *new default*

5 – flat Batch Key Access (BKA)

6 – incremental BKA

7 – flat Batch Key Access Hash (BKAH)

8 – incremental BKAH

optimizer_use_condition_selectivity:

1 Use selectivity of predicates as in MariaDB 5.5. -- *current default *

2 Use selectivity of all range predicates supported by indexes.

3 Use selectivity of all range predicates estimated without histogram.

4 Use selectivity of all range predicates estimated with histogram. -- *new default *

5 Additionally use selectivity of certain non-range predicates calculated on record sample.

Note that histograms are not collected automatically, so the value of 4 will have the same effect as 3 for those who never collected histograms.

TODO: As a second step, should histogram_size default be changed from 0 to 255 ?

If optimizer_use_condition_selectivity=4 then it would seem a little odd to keep histogram_size at 0. 255 seems like a good default to cover a broad range of values in a column with little cost if this isn't the case.

Milestone1: Increase the value for join_cache_level from 2 to 4

varun de7a3b23ba66e0946adde0906f1972e80b0e700a had the wrong MDEV ref.

Well we are not going to change the value for join_cache_level. It will still remain to be 2. We are not having any improved performance gains here because there is no cost based approach for hash join and we unconditionally always prefer hash join.

The branch where all the changes is 10.4-selectivity4

One issue we came across while changing the defaults that ANALYZE on a table would become quite slow , because it will also calculate the EITS at the same time.

According to the docs:

With engine-independent statistics:

If @@use_stat_tables='never' and PERSISTENT FOR isn't used as part of the ALTER TABLE statement, then only storage engine statistics will be updated and not engine-independent statistics.

For other values of @@use_stat_tables, both storage engine statistics and engine-independent statistics will be updated.

Pushed to 10.4