Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.2
Description
The optimizer seems not to be aware of how virtual columns can use indexes. I don't know if this optimization is doable for WHERE or ORDER BY. But here is a case where I think that a covering index should be used:
CREATE OR REPLACE TABLE t ( |
v INT AS (a + b) VIRTUAL, |
a INT, |
b INT, |
c INT, |
d INT, |
INDEX idx_1 (a, b) |
) ENGINE = InnoDB;
|
|
|
INSERT INTO t (a, b, c, d) VALUES (RAND()*100, RAND()*100, RAND()*100, RAND()*100); |
INSERT INTO t (a, b, c, d) SELECT a, b, c, d FROM t; -- multiple times |
|
|
-- add WHERE clauses if you like
|
MariaDB [test]> EXPLAIN SELECT a + b FROM t; |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ |
| 1 | SIMPLE | t | index | NULL | idx_1 | 10 | NULL | 256 | Using index | |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> EXPLAIN SELECT v FROM t; |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+ |
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 256 | | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+ |
1 row in set (0.00 sec) |
Could the optimizer be informed that the two queries do exactly the same thing?