Details
Blocker Description
This comes from Monty who got a complaint from a user
After MDEV-28846, rowid filtering optimization is applied in the cases where it should not be.
Also, reading the optimizer code, one can see obvious cost computation errors.
Attachments
Issue Links
- includes
-
MDEV-30142 rowid_filter: negative access cost factor values
-
- Closed
-
-
-
- Open
-
Activity
There must be a test case, proving that the new code makes queries faster
The following test case
--source include/have_innodb.inc
|
--source include/have_sequence.inc
|
|
|
create table t1 (p int primary key, a int, b int, unique(a),key(b)) engine=innodb; |
insert into t1 select seq,seq,seq/10 from seq_1_to_100000; |
analyze table t1; |
explain select straight_join count(*) from t1 as t3, t1, t1 as t2 where t1.a=t2.a and t2.b between 1 and 1500; |
set @now=unix_timestamp(now(6)); |
select straight_join count(*) from t1 as t3, t1, t1 as t2 where t1.a=t2.a and t2.b between 1 and 1500; |
select unix_timestamp(now(6))-@now; |
drop table t1; |
becomes 15% slower after the patch
+------+-------------+-------+---------------+---------------+------+---------+-----------+---------+----------+--------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+---------------+---------------+------+---------+-----------+---------+----------+--------------------------------------------------------------+
|
| 1 | SIMPLE | t3 | index | NULL | a | 5 | NULL | 100256 | 100.00 | Using index |
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 50128 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t2 | eq_ref|filter | a,b | a|b | 5|5 | test.t1.a | 1 (28%) | 27.86 | Using where; Using rowid filter |
|
+------+-------------+-------+---------------+---------------+------+---------+-----------+---------+----------+--------------------------------------------------------------+
|
A testcase like just like the above one from serg where original code (before the patch for this MDEV) will not use rowid filter, despite that the query plan is chooses is the same in all other aspects:
(This demonstrates a bug that eq_ref code uses the value of keyread_tmp that is left over from the previously considered index, whatever it was.)
create table t1 ( |
p int primary key, |
badcol1 int, |
badcol2 int, |
a int, |
b int, |
unique(badcol1, badcol2), |
unique(a), |
key(b) |
) engine=innodb;
|
insert into t1 select seq, (seq/30000), seq, seq,seq/10 from seq_1_to_100000; |
analyze table t1; |
|
|
explain
|
select straight_join count(*) |
from |
t1 as t3, t1, t1 as t2 |
where |
t1.a=t2.a and |
t2.b between 1 and 1500 and |
t1.badcol1=t2.badcol1 ;
|
Part#2 patch:
https://github.com/MariaDB/server/commit/678f2220cc25b3495f1f16fe2732e49a0cd120f8
The change in subselect2.result looks odd. I've investigated - it has nothing
to do with rowid filtering code. The ref|filter access was estimated as eq_ref|filter (and it should have been eq_ref|filter). Filed MDEV-30476 with all the details.
My poking around in the related code was logged as
MDEV-30142.