|
Hi,
while writing [1], I did some test with MariaDB and I think I found a regression. A query using ICP in MySQL is not using ICP in MariaDB 10.{2,3,4,5,6,7} according to explain, but according to timing, something is much faster in 10.{2,3,4} (0.2s for running the query 100 times) than in 10.{5,6,7} (3s for running the query 100 times).
[1]: https://jfg-mysql.blogspot.com/2022/03/rows-examined-not-trustworthy-because-index-condition-pushdown.html
Also, from what I saw, MariaDB is also impacted from what I described in [1] and in Bug#104659 [2]. I am not opening a dedicated Jira for this, I will let you decide what is the best way forward.
[2]: https://bugs.mysql.com/bug.php?id=104659
I am reporting this as a bug for all MariaDB 10.{7,6,5,4,3,2} because I think 10.{7,6,5} should run this query much faster and I think 10.{4,3,2} should have "Using index condition" in the explain.
Also, I am reporting this bug as Major because there is a very big performance regression between 10.4 and the next version (more than 10 times slower).
Many thanks for looking into this,
Jean-François Gagné
—
Below, reproduction for 10.7.3 with the matching output for 10.4.24, 10.6.7 and 10.5.15, 10.3.34 and 10.2.43.
# Create a sandbox.
|
dbdeployer deploy single mariadb_10.7.3 -c 'innodb_flush_log_at_trx_commit = 2'
|
|
# Initialize the test environment.
|
./use <<< "
|
CREATE DATABASE test_jfg;
|
CREATE TABLE test_jfg.t (
|
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
v1 INTEGER NOT NULL,
|
v2 INTEGER NOT NULL,
|
v3 INTEGER NOT NULL,
|
v4 INTEGER NOT NULL,
|
KEY v1_to_v3 (v1, v2, v3))"
|
|
# Insert 100,000 rows in the table with random v1 and v3 between 0 and 9 inclusive.
|
seq 1 100000 |
|
awk 'BEGIN{srand(0)} {print "(" int(rand()*10), $1, int(rand()*10), $1 ")"}' |
|
tr " " "," | paste -s -d "$(printf ',%.0s' {1..100})\n" |
|
sed -e 's/.*/INSERT into t(v1,v2,v3,v4) values &;/' |
|
./use test_jfg
|
|
# Set v3 to 10 on 2 random rows where v1 = 0.
|
./use -N test_jfg <<< "SELECT id FROM t WHERE v1 = 0" |
|
sort -R | head -n 2 | paste -s -d "," |
|
while read l; do echo "UPDATE t SET v3 = 10 WHERE id IN ($l)"; done |
|
./use test_jfg
|
|
# Show the query is not using ICP.
|
./use test_jfg -e "SELECT version(); EXPLAIN SELECT v2 FROM t WHERE v1 = 0 AND v3 = 10\G"
|
+----------------+
|
| version() |
|
+----------------+
|
| 10.7.3-MariaDB |
|
+----------------+
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t
|
type: ref
|
possible_keys: v1_to_v3
|
key: v1_to_v3
|
key_len: 4
|
ref: const
|
rows: 18174
|
Extra: Using where; Using index
|
|
# Time running that query 100 times.
|
time yes "SELECT * FROM t WHERE v1 = 0 AND v3 = 10;" | head -n 100 | ./use test_jfg > /dev/null
|
|
real 0m3.235s
|
user 0m0.011s
|
sys 0m0.004s
|
|
# Results for 10.4.24: explain showing not using ICP, but the query is much faster, which makes me think it is using ICP.
|
./use test_jfg -e "SELECT version(); EXPLAIN SELECT v2 FROM t WHERE v1 = 0 AND v3 = 10\G"
|
+-----------------+
|
| version() |
|
+-----------------+
|
| 10.4.24-MariaDB |
|
+-----------------+
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t
|
type: ref
|
possible_keys: v1_to_v3
|
key: v1_to_v3
|
key_len: 4
|
ref: const
|
rows: 18174
|
Extra: Using where; Using index
|
|
time yes "SELECT * FROM t WHERE v1 = 0 AND v3 = 10;" | head -n 100 | ./use test_jfg > /dev/null
|
|
real 0m0.225s
|
user 0m0.010s
|
sys 0m0.004s
|
|
# Results for 10.6.7: explain not showing ICP and the query is slow.
|
./use test_jfg -e "SELECT version(); EXPLAIN SELECT v2 FROM t WHERE v1 = 0 AND v3 = 10\G"
|
+----------------+
|
| version() |
|
+----------------+
|
| 10.6.7-MariaDB |
|
+----------------+
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t
|
type: ref
|
possible_keys: v1_to_v3
|
key: v1_to_v3
|
key_len: 4
|
ref: const
|
rows: 18174
|
Extra: Using where; Using index
|
|
time yes "SELECT * FROM t WHERE v1 = 0 AND v3 = 10;" | head -n 100 | ./use test_jfg > /dev/null
|
|
real 0m3.096s
|
user 0m0.010s
|
sys 0m0.005s
|
|
# Results for 10.5.15: explain not showing ICP and then query is slow..
|
./use test_jfg -e "SELECT version(); EXPLAIN SELECT v2 FROM t WHERE v1 = 0 AND v3 = 10\G"
|
+-----------------+
|
| version() |
|
+-----------------+
|
| 10.5.15-MariaDB |
|
+-----------------+
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t
|
type: ref
|
possible_keys: v1_to_v3
|
key: v1_to_v3
|
key_len: 4
|
ref: const
|
rows: 18174
|
Extra: Using where; Using index
|
|
time yes "SELECT * FROM t WHERE v1 = 0 AND v3 = 10;" | head -n 100 | ./use test_jfg > /dev/null
|
|
real 0m3.202s
|
user 0m0.007s
|
sys 0m0.010s
|
|
# Result for 10.3.34: explain showing not using ICP, but the query is much faster, which makes me think it is using ICP.
|
./use test_jfg -e "SELECT version(); EXPLAIN SELECT v2 FROM t WHERE v1 = 0 AND v3 = 10\G"
|
+-----------------+
|
| version() |
|
+-----------------+
|
| 10.3.34-MariaDB |
|
+-----------------+
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t
|
type: ref
|
possible_keys: v1_to_v3
|
key: v1_to_v3
|
key_len: 4
|
ref: const
|
rows: 18174
|
Extra: Using where; Using index
|
|
time yes "SELECT * FROM t WHERE v1 = 0 AND v3 = 10;" | head -n 100 | ./use test_jfg > /dev/null
|
|
real 0m0.210s
|
user 0m0.010s
|
sys 0m0.003s
|
|
# Result for 10.2.43: explain showing not using ICP, but the query is much faster, which makes me think it is using ICP.
|
./use test_jfg -e "SELECT version(); EXPLAIN SELECT v2 FROM t WHERE v1 = 0 AND v3 = 10\G"
|
+-----------------+
|
| version() |
|
+-----------------+
|
| 10.2.43-MariaDB |
|
+-----------------+
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t
|
type: ref
|
possible_keys: v1_to_v3
|
key: v1_to_v3
|
key_len: 4
|
ref: const
|
rows: 18174
|
Extra: Using where; Using index
|
|
time yes "SELECT * FROM t WHERE v1 = 0 AND v3 = 10;" | head -n 100 | ./use test_jfg > /dev/null
|
|
real 0m0.206s
|
user 0m0.002s
|
sys 0m0.011s
|
|