column stats make the query slower

create table t1 (a int, b int);

insert t1 values (rand()*1e5, rand()*1e5);

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

insert t1 select rand()*1e5, rand()*1e5 from t1;

set use_stat_tables=preferably;

analyze table t1;

create table t2 (c int, d int, key(c), key(d));

insert t2 select floor(rand()*1e5/2)*2, floor(rand()*1e5/3)*3 from t1;

set optimizer_use_condition_selectivity=1;

explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;

select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;

set optimizer_use_condition_selectivity=3;

explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;

select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;

MariaDB [test]> set optimizer_use_condition_selectivity=3;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;

+------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref       | rows   | filtered | Extra                                           |

+------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      | 524288 |     2.00 | Using where                                     |

|    1 | SIMPLE      | t3    | ALL  | NULL          | NULL | NULL    | NULL      | 524288 |     0.00 | Using where; Using join buffer (flat, BNL join) |

|    1 | SIMPLE      | t2    | ref  | c,d           | c    | 5       | test.t1.b |     10 |   100.00 | Using where                                     |

+------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+

3 rows in set, 1 warning (0.00 sec)

MariaDB [test]> set optimizer_use_condition_selectivity=1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;

+------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref       | rows   | filtered | Extra                                           |

+------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      | 524288 |   100.00 | Using where                                     |

|    1 | SIMPLE      | t2    | ref  | c,d           | c    | 5       | test.t1.b |     10 |   100.00 |                                                 |

|    1 | SIMPLE      | t3    | ALL  | NULL          | NULL | NULL    | NULL      | 524288 |   100.00 | Using where; Using join buffer (flat, BNL join) |

+------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+

3 rows in set, 1 warning (0.00 sec)