According to 17.6.2.1 Clustered and Secondary Indexes,
Indexes other than the clustered index are known as secondary indexes
This means there are only 2 types of indexes: clustered and secondary.
However, 15.1.20.9 Secondary Indexes and Generated Columns says
InnoDB supports secondary indexes on virtual generated columns. Other index types are not supported.
The plural form “other index type s” implies there are at least 3 index types (secondary + something + something).
Which is correct?
If the latter is correct, what are the other types of indexes specifically other than the secondary index?
6
The only other index types are SPATIAL and FULLTEXT.
You can’t create these types of indexes on generated columns.
mysql> create table mytable (id serial primary key, t text, t2 text as (substring(t, 1024)));
mysql> create fulltext index i on mytable(t2);
ERROR 3106 (HY000): 'Fulltext index on virtual generated column' is not supported for generated columns.
mysql> create table mytable (id serial primary key, p point as (point(1,2)));
Query OK, 0 rows affected (0.01 sec)
mysql> create spatial index i on mytable(p);
ERROR 3106 (HY000): 'Spatial index on virtual generated column' is not supported for generated columns.
2