[Maria-developers] Optimizer call followup: negative gains from rowid filters
Hello,
Just taking notes from the optimizer call:
Check out this branch: 10.4-mdev30218-print-negative-gain (git log -3
output below)
It has:
- 10.4-release contents
- then mdev30218_bb-10.4-release.diff patch provided by Igor
- then a debug patch by me.
I run:
./mtr --mem --parallel=5 --force main/rowid_filter*test
grep -rn filter_gain var/
I get this: negative gains from filters:
var/4/log/mysqld.1.err:52:filter_gain:-0.0569276, query=select 1
var/4/log/mysqld.1.err:58:filter_gain:-0.0569276, query=explain extended select 1
var/4/log/mysqld.1.err:64:filter_gain:-0.250816, query=SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3)
var/4/log/mysqld.1.err:65:filter_gain:-0.250816, query=EXPLAIN EXTENDED
var/4/log/mysqld.1.err:67:filter_gain:-10.207, query=explain
var/4/log/mysqld.1.err:69:filter_gain:-10.207, query=SELECT * FROM t1 WHERE a > 0 AND b=0
var/4/log/mysqld.1.err:70:filter_gain:-1.17502, query=select count(*) from t1 where a between 21 and 30 and b=2
var/4/log/mysqld.1.err:71:filter_gain:-1.17502, query=explain extended select count(*) from t1 where a between 21 and 30 and b=2
var/4/log/mysqld.1.err:72:filter_gain:-1.17502, query=select * from t1 where a between 21 and 30 and b=2
var/4/log/mysqld.1.err:73:filter_gain:-2.18259, query=SELECT * FROM t1
var/4/log/mysqld.1.err:77:filter_gain:-2.18259, query=EXPLAIN EXTENDED SELECT * FROM t1
var/4/log/mysqld.1.err:81:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.*
var/4/log/mysqld.1.err:90:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
var/4/log/mysqld.1.err:99:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.*
var/4/log/mysqld.1.err:108:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
var/4/log/mysqld.1.err:117:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for analyze format=json select t.id, fi.*
var/3/log/mysqld.1.err:51:filter_gain:-0.0569276, query=select 1
var/3/log/mysqld.1.err:57:filter_gain:-0.0569276, query=explain extended select 1
var/3/log/mysqld.1.err:63:filter_gain:-0.250816, query=SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3)
var/3/log/mysqld.1.err:64:filter_gain:-0.250816, query=EXPLAIN EXTENDED
var/3/log/mysqld.1.err:66:filter_gain:-12.7213, query=explain
var/3/log/mysqld.1.err:68:filter_gain:-12.7213, query=SELECT * FROM t1 WHERE a > 0 AND b=0
=== git log -3 output ===
commit 82d5977b9fd6be5f5152d6831cd744fc739a2f24 (HEAD -> 10.4-mdev30218-print-negative-gain, origin/10.4-mdev30218-print-negative-gain, bb-10.4-release)
Author: Sergei Petrunia
participants (1)
-
Sergey Petrunia