|
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...
|
|
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().
|
|
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
|