Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 12.2.2
-
Related to performance
Description
Two logically equivalent queries have significantly different execution times after adding OR FALSE to the WHERE clause.
The original query uses:
WHERE b.v IN (SELECT v FROM b WHERE v <> 68) |
The mutated query uses:
WHERE (b.v IN (SELECT v FROM b WHERE v <> 68)) OR FALSE |
These predicates are logically equivalent. However, MariaDB chooses a worse plan for the OR FALSE version. The original query starts from table b and uses the materialized IN subquery efficiently, while the OR FALSE version starts from the larger table a, causing more join work.
In my test, both queries return the same result, but the OR FALSE version is consistently about 2.5x slower.
How to repeat
DROP DATABASE IF EXISTS perf_or_false_left_join_in_int;
|
CREATE DATABASE perf_or_false_left_join_in_int;
|
USE perf_or_false_left_join_in_int;
|
|
|
CREATE TABLE a(
|
id INT
|
);
|
|
|
CREATE TABLE b(
|
id INT,
|
v INT
|
);
|
|
|
CREATE INDEX b_id ON b(id);
|
|
|
CREATE TABLE d(
|
n INT
|
);
|
|
|
INSERT INTO d VALUES
|
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
|
|
INSERT INTO a
|
SELECT x.n * 1000 + y.n * 100 + z.n * 10 + w.n |
FROM d AS x
|
JOIN d AS y
|
JOIN d AS z
|
JOIN d AS w;
|
|
|
INSERT INTO b
|
SELECT x.n * 10 + y.n, x.n * 10 + y.n |
FROM d AS x
|
JOIN d AS y;
|
|
|
SET profiling = 1; |
|
|
SELECT SQL_NO_CACHE COUNT(*)
|
FROM a
|
LEFT JOIN b
|
ON b.id NOT BETWEEN 13 AND 19 |
WHERE b.v IN (
|
SELECT v
|
FROM b
|
WHERE v <> 68 |
);
|
|
|
SELECT SQL_NO_CACHE COUNT(*)
|
FROM a
|
LEFT JOIN b
|
ON b.id NOT BETWEEN 13 AND 19 |
WHERE (
|
b.v IN (
|
SELECT v
|
FROM b
|
WHERE v <> 68 |
)
|
) OR FALSE;
|
|
|
SHOW PROFILES;
|
|
|
EXPLAIN
|
SELECT SQL_NO_CACHE COUNT(*)
|
FROM a
|
LEFT JOIN b
|
ON b.id NOT BETWEEN 13 AND 19 |
WHERE b.v IN (
|
SELECT v
|
FROM b
|
WHERE v <> 68 |
);
|
|
|
EXPLAIN
|
SELECT SQL_NO_CACHE COUNT(*)
|
FROM a
|
LEFT JOIN b
|
ON b.id NOT BETWEEN 13 AND 19 |
WHERE (
|
b.v IN (
|
SELECT v
|
FROM b
|
WHERE v <> 68 |
)
|
) OR FALSE;
|
Observed result
Query 1 result: COUNT(*) = 920000 |
Query 2 result: COUNT(*) = 920000 |
Query 1 durations:
0.02960448 sec |
0.02729056 sec |
0.02679830 sec |
Query 2 durations:
0.07487734 sec |
0.07509202 sec |
0.07677743 sec |
Plan difference
Original query:
PRIMARY b
|
PRIMARY <subquery2> eq_ref distinct_key
|
PRIMARY a
|
SUBQUERY b MATERIALIZED
|
With OR FALSE:
PRIMARY a
|
PRIMARY b
|
SUBQUERY b MATERIALIZED
|
Expected result
Adding OR FALSE should not prevent the optimizer from applying the same IN subquery optimization or choosing the same efficient join order. Both logically equivalent queries should have comparable execution time.