Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.2.2
-
None
-
Not for Release Notes
Description
Two logically equivalent queries return the same result, but their execution times differ dramatically only because the OR predicates are ordered differently and one cheap predicate is duplicated.
In the slower query, MariaDB evaluates an expensive expression REPEAT(s,n) before reaching the cheap condition z < 85. In the faster query, the cheap true condition z < 85 appears first, so the expensive expression can effectively be avoided by short-circuit evaluation.
The optimizer appears not to normalize, reorder, or deduplicate the logically equivalent OR predicates. As a result, two equivalent WHERE clauses can have a very large and unexpected runtime difference.
In my test, both queries returned COUNT
= 1000, but the first query took about 0.0975 sec, while the second query took about 0.00023 sec, over 400x faster.
How to repeat
DROP DATABASE IF EXISTS perf_or_order;
|
CREATE DATABASE perf_or_order;
|
USE perf_or_order;
|
|
|
CREATE TABLE t(
|
s VARCHAR(10), |
n INT,
|
z INT
|
);
|
|
|
CREATE TABLE d(n INT);
|
|
|
INSERT INTO d VALUES
|
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
|
|
INSERT INTO t
|
SELECT 'x', 50000, 0 |
FROM d a JOIN d b JOIN d c;
|
|
|
SET profiling = 1; |
|
|
SELECT SQL_NO_CACHE COUNT(*) FROM (
|
SELECT REPEAT(s,n) AS q4, z
|
FROM t
|
) AS sq
|
WHERE
|
((q4 = 'sample_35' OR q4 <> 'sample_12') AND z IS NOT NULL) |
OR z < 85; |
|
|
SELECT SQL_NO_CACHE COUNT(*) FROM (
|
SELECT REPEAT(s,n) AS q4, z
|
FROM t
|
) AS sq
|
WHERE
|
z < 85 |
OR ((q4 = 'sample_35' OR q4 <> 'sample_12') AND z IS NOT NULL) |
OR z < 85; |
|
|
SHOW PROFILES;
|
Observed result
Query 1 result: COUNT(*) = 1000 |
Query 2 result: COUNT(*) = 1000 |
|
|
Query 1 duration: about 0.0975 sec |
Query 2 duration: about 0.00023 sec |
Expected result
Since the two predicates are logically equivalent, MariaDB should ideally normalize or simplify the duplicated/commuted OR condition so that both queries get comparable execution time. At minimum, the redundant duplicated predicate should not cause such a large performance difference.
Attachments
Issue Links
- duplicates
-
MDEV-40433 Optimizer does not eliminate duplicate identical IN subquery predicates, causing repeated full scan
-
- Confirmed
-