Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8326

Covering indexes never used when selecting a Virtual Column

        Details

        • Type: Task
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Fix Version/s: None
        • Component/s: Optimizer
        • Labels:

          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?

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    f_razzoli Federico Razzoli
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: