Details
-
New Feature
-
Status: In Testing (View Workflow)
-
Critical
-
Resolution: Unresolved
Description
This MDEV covers basic optimizer support for virtual columns added in 11.8.
If a user declares a virtual column and an index on it
ALTER TABLE t1 |
ADD COLUMN vcol INT AS (col1+1), |
ADD INDEX idx1(vcol); |
and then a query uses the exact same expression:
SELECT * FROM t1 WHERE col1+1 <= 100 |
Then the optimizer will rewrite the above into
... WHERE vcol <= 100 |
which enables the rest of the optimizer to construct query plans that use index idx1.
The rewrite is performed in WHERE/ON clauses (and only there).
The rewrite targets indexed virtual columns.
The rewrite targets a subset of conditions usable by the range optimizer:
vcol_expr $CMP const1 where $CMP is = or [less|greater][or equal].
|
vcol_expr BETWEEN const1 AND const2
|
vcol_expr IN (const-list)
|
vcol_expr IS [NOT] NULL
|
Attachments
Issue Links
- is part of
-
MDEV-6017 Add support for Indexes on Expressions
- Stalled
- relates to
-
MDEV-35615 Query optimizer support for functional indexes: next steps
- Open
-
MDEV-35496 JSON_UNQUOTE produces utf8mb3 output while JSON can be utf8mb4
- Confirmed
The commit is in bb-11.8-MDEV-6017-variant3 branch:
commit 75773e1d28877d7f3c4599b554921c472adcc6bb (HEAD -> bb-11.8-MDEV-6017-variant3, origin/bb-11.8-MDEV-6017-variant3)
Author: Sergei Petrunia <sergey@mariadb.com>
Date: Tue Nov 26 14:50:41 2024 +0200
MDEV-35616: Add basic optimizer support for virtual column
(Review input addressed)
After this patch, the optimizer can handle virtual column expressions
in WHERE/ON clauses. If the table has an indexed virtual column:
ALTER TABLE t1
ADD COLUMN vcol INT AS (col1+1),
ADD INDEX idx1(vcol);
and the query uses the exact virtual column expression:
SELECT * FROM t1 WHERE col1+1 <= 100
then the optimizer will be able use index idx1 for it.
This is achieved by walking the WHERE/ON clauses and replacing instances
of virtual column expression (like "col1+1" above) with virtual column's
Item_field (like "vcol"). The latter can be processed by the optimizer.
Replacement is considered (and done) only in items that are potentially
usable to the range optimizer.