[MDEV-8326] Covering indexes never used when selecting a Virtual Column Created: 2015-06-17  Updated: 2023-10-04

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.2

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: virtual_columns


 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?

 Comments   
Comment by Brad Jorgensen [ 2015-12-29 ]

It should use the covering index, but it might not be using it due to the low amount of data. Does it use the covering index if you have the same table without the virtual column?

Comment by Alice Sherepa [ 2017-04-27 ]

index is not used, 5.5-10.2 

 CREATE TABLE t1 (a int, w int AS (a+0) VIRTUAL);
 
 ALTER TABLE t1 ADD INDEX a(a);
 
 INSERT INTO `t1` (`a`) VALUES (98),(50),(91),(75),(74),(14),(25),(99),(57),(90);
 
 
 
 EXPLAIN SELECT a+0 FROM t1 WHERE a=75;
 
 EXPLAIN SELECT w FROM t1 WHERE a=75; 

MariaDB [test]>  EXPLAIN SELECT a+0 FROM t1 WHERE a=75;
 
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
 
|    1 | SIMPLE      | t1    | ref  | a             | a    | 5       | const |    1 | Using index |
 
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]>  EXPLAIN SELECT w FROM t1 WHERE w=75;
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.00 sec)

Generated at Thu Feb 08 07:26:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.