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

Covering indexes never used when selecting a Virtual Column

    XMLWordPrintable

    Details

      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?

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated: