Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.2.2
-
None
-
Not for Release Notes
Description
Two semantically equivalent queries show a stable performance difference. The second query only duplicates the same EXISTS predicate with AND, so the result is unchanged. However, MariaDB materializes the same subquery twice.
Observed on 12.2.2-MariaDB-ubu2404:
original: 0.0215s |
mutated: 0.0449s |
EXPLAIN shows:
original: one MATERIALIZED subquery over inner_t
|
mutated: two MATERIALIZED subqueries over inner_t
|
How to repeat
DROP DATABASE IF EXISTS perf_exists_dup;
|
CREATE DATABASE perf_exists_dup;
|
USE perf_exists_dup;
|
|
|
CREATE TABLE outer_t(id INT PRIMARY KEY);
|
CREATE TABLE inner_t(v INT, pad CHAR(20)); |
|
|
CREATE TABLE d(n INT);
|
INSERT INTO d VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
|
|
INSERT INTO outer_t
|
SELECT a.n + 10*b.n + 100*c.n |
FROM d AS a JOIN d AS b JOIN d AS c;
|
|
|
INSERT INTO inner_t
|
SELECT (a.n + 10*b.n + 100*c.n + 1000*d1.n + 10000*e.n) % 1000, |
REPEAT('x',20) |
FROM d AS a
|
JOIN d AS b
|
JOIN d AS c
|
JOIN d AS d1
|
JOIN d AS e;
|
|
|
EXPLAIN
|
SELECT COUNT(*)
|
FROM outer_t AS o
|
WHERE EXISTS (
|
SELECT 1 |
FROM inner_t AS i
|
WHERE i.v = o.id
|
AND i.pad = 'xxxxxxxxxxxxxxxxxxxx' |
);
|
|
|
EXPLAIN
|
SELECT COUNT(*)
|
FROM outer_t AS o
|
WHERE EXISTS (
|
SELECT 1 |
FROM inner_t AS i
|
WHERE i.v = o.id
|
AND i.pad = 'xxxxxxxxxxxxxxxxxxxx' |
)
|
AND EXISTS (
|
SELECT 1 |
FROM inner_t AS i
|
WHERE i.v = o.id
|
AND i.pad = 'xxxxxxxxxxxxxxxxxxxx' |
);
|
|
|
SET profiling = 1; |
|
|
SELECT COUNT(*)
|
FROM outer_t AS o
|
WHERE EXISTS (
|
SELECT 1 |
FROM inner_t AS i
|
WHERE i.v = o.id
|
AND i.pad = 'xxxxxxxxxxxxxxxxxxxx' |
);
|
|
|
SELECT COUNT(*)
|
FROM outer_t AS o
|
WHERE EXISTS (
|
SELECT 1 |
FROM inner_t AS i
|
WHERE i.v = o.id
|
AND i.pad = 'xxxxxxxxxxxxxxxxxxxx' |
)
|
AND EXISTS (
|
SELECT 1 |
FROM inner_t AS i
|
WHERE i.v = o.id
|
AND i.pad = 'xxxxxxxxxxxxxxxxxxxx' |
);
|
|
|
SHOW PROFILES;
|
Expected:
The optimizer should recognize the duplicated identical EXISTS predicate and materialize/evaluate it once.
Observed:
The duplicated version materializes the same subquery twice and runs about 2x slower.
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-