Sergei Petrunia added a comment - 2019-08-29 19:45 Making EXPLAINs a bit more readable. MariaDB 10.4: table type key rows r_rows filtered r_filtered 1 SIMPLE ticketgrou0_ range index_ticket_group_on_business_id... 1770 1770.00 0.00 100.00 Using where; Using index 1 SIMPLE tables0_ ref index_ticket_group_table_on_tg_id 1 1.24 100.00 100.00 Using where; Using index 1 SIMPLE tableentit1_ ALL NULL 66037 66280.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) Same query on MariaDB 10.3: table type key rows r_rows filtered r_filtered 1 PRIMARY ticketgrou0_ range index_ticket_group_on_business_id... 1770 1770.00 100.00 100.00 Using where; Using index 1 PRIMARY tables0_ ref index_ticket_group_table_on_tg_id 1 1.24 100.00 100.00 Using index condition 1 PRIMARY tableentit1_ eq_ref PRIMARY 1 1.00 100.00 100.00 Using where Note that "filtered" is 100% here (so why does optimizer_use_condition_selectivity play a role)? The incoming fanout into the 3rd table is 1.7K rows... and then: MariaDB 10.3 chooses eq_ref access, 1.7K index lookups MariaDB 10.4 chooses to do full table scan, with join buffer. I assume 1.7K row combinations will fit into join buffer (default size is 256K), so the table will be scanned once. The table has 66K rows, so there will be 66K * 1.7K = 112M row combinations to consider. Which should have been much more expensive than eq_ref access...

Varun Gupta (Inactive) added a comment - 2019-08-29 23:23 Filtered for tickettgroup0_ shows 0.00, this for the case when optimizer_use_condition_selectivity > 1 is the selectivity of conditions when joining a table, this for our case should be 100%, this looks incorrect, this would also set incorrect values for record_count(records in the prefix), so looks like the bug is in table_cond_selectivity().

Martin Štěpař added a comment - 2019-10-08 12:11 I confirm this issue, have similar query and with optimizer_use_condition_selectivity=4 is full scan performed. Even FORCE INDEX is ignored.

Varun Gupta (Inactive) added a comment - 2019-10-23 12:05 - edited Reproduced by this mtr test case: --source include/have_sequence.inc   create table t1(a int , b int , c int , d int , key (a,b)); insert into t1 select 50,seq-1,seq-1,seq from seq_1_to_100; insert into t1 select seq-1,seq-1,seq-1,seq from seq_1_to_1000 limit 900;   create table t2(a int , b int , c int , primary key (a)); insert into t2 select seq-1,seq-1,seq-1 from seq_1_to_100;   create table t3(a int , b int , c int , primary key (a)); insert into t3 select seq-1,seq-1,seq-1 from seq_1_to_100 limit 30;   let $query= select * from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100;   set optimizer_use_condition_selectivity=1; eval explain extended $query; set optimizer_use_condition_selectivity=2; eval explain extended $query;   drop table t1,t2,t3; MariaDB [test]> set optimizer_use_condition_selectivity=2; Query OK, 0 rows affected (0.001 sec)   MariaDB [test]> explain extended select * from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------------------------------------------------+ | 1 | SIMPLE | t1 | range | a | a | 10 | NULL | 101 | 10.10 | Using index condition; Using where | | 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.c | 1 | 100.00 | | | 1 | SIMPLE | t3 | ALL | PRIMARY | NULL | NULL | NULL | 30 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------------------------------------------------+ 3 rows in set, 1 warning (0.006 sec)   MariaDB [test]> set optimizer_use_condition_selectivity=1; Query OK, 0 rows affected (0.001 sec)   MariaDB [test]> explain extended select * from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+------------------------------------+ | 1 | SIMPLE | t1 | range | a | a | 10 | NULL | 101 | 100.00 | Using index condition; Using where | | 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.c | 1 | 100.00 | | | 1 | SIMPLE | t3 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.d | 1 | 100.00 | | +------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+------------------------------------+ 3 rows in set, 1 warning (0.005 sec) With optimizer_use_condition_selectivity >=1 we pick table scan over table t3 rather than eq_ref access

Varun Gupta (Inactive) added a comment - 2019-10-27 06:04 Patch http://lists.askmonty.org/pipermail/commits/2019-October/014035.html

Sergei Petrunia added a comment - 2019-10-31 07:12 Ok to push