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)
Friday, December 10, 2010
Indexes on virtual columns in MariaDB 5.2
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment