Friday, December 10, 2010

Indexes on virtual columns in MariaDB 5.2

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table x(a int not null primary key,
    ->    b varchar(32),
    ->    c int as (a mod 5) virtual,
    ->    d int as (a mod 7) persistent
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> describe x;
+-------+-------------+------+-----+---------+---------+
| Field | Type        | Null | Key | Default | Extra   |
+-------+-------------+------+-----+---------+---------+
| a     | int(11)     | NO   | PRI | NULL    |         |
| b     | varchar(32) | YES  |     | NULL    |         |
| c     | int(11)     | YES  |     | NULL    | VIRTUAL |
| d     | int(11)     | YES  |     | NULL    | VIRTUAL |
+-------+-------------+------+-----+---------+---------+
4 rows in set (0.03 sec)

MariaDB [test]> insert into x(a,b) values(1,1),(20,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from x;
+----+------+------+------+
| a  | b    | c    | d    |
+----+------+------+------+
|  1 | 1    |    1 |    1 |
| 20 | 20   |    0 |    6 |
+----+------+------+------+
2 rows in set (0.00 sec)

MariaDB [test]> create index c on x(c);
ERROR 1645 (HY000): Key/Index cannot be defined on a non-stored computed column
MariaDB [test]> create index d on x(d);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> explain select * from x where d = 6;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | x     | ref  | c             | c    | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

No comments: