6.5.7 CREATE INDEX Syntax

CREATE [UNIQUE|FULLTEXT] INDEX index_name
       ON tbl_name (col_name[(length)],... )

The CREATE INDEX statement doesn't do anything in MySQL prior to Version 3.22. In Version 3.22 or later, CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See section 6.5.4 ALTER TABLE Syntax.

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See section 6.5.3 CREATE TABLE Syntax. CREATE INDEX allows you to add indexes to existing tables.

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns.

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax to index the first length bytes of each column value. (For BLOB and TEXT columns, a prefix length is required; length may be a value up to 255.) The statement shown here creates an index using the first 10 characters of the name column:

mysql> CREATE INDEX part_of_name ON customer (name(10));

Because most names usually differ in the first 10 characters, this index should not be much slower than an index created from the entire name column. Also, using partial columns for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations!

Note that you can only add an index on a column that can have NULL values or on a BLOB/TEXT column if you are using MySQL Version 3.23.2 or newer and are using the MyISAM table type.

For more information about how MySQL uses indexes, see section 5.4.3 How MySQL Uses Indexes.

FULLTEXT indexes can index only VARCHAR and TEXT columns, and only in MyISAM tables. FULLTEXT indexes are available in MySQL Version 3.23.23 and later. section 6.8 MySQL Full-text Search.

User Comments

At least with MySQL 4.1a
CREATE FULLTEXT INDEX ...
returns an error, however
ALTER TABLE ... ADD FULLTEXT ...
works.

That line near the end should read "Because most names usually differ in the first 10 characters, this index should not be much FASTER than an index created from the entire name column."

Actually, that last comment by colin knox is wrong. The original text is correct. A truncated index will be typically smaller, BUT slightly SLOWER than a regular (non truncated) index. This is because the truncated index will take more time to find an item in the cases where the item appears multiple times in the index (because other items have the same truncated data.)
In summary, it is recommended to use a truncated index when you expect the prefixes of the data indexed to be mostly different, which is typically the case with strings. That is, the slight speed increase is not worth the extra index size.

Regarding the previous two posts, I believe that if the number of bytes per index entry is halved, then twice as many index entries will fit on a page (more or less), resulting in a more shallow btree, with less disk I/O both on insert and select, and more effective index caching. It would seem that this should speed up performance noticably as long as the number of collisions is relatively low (<10%) and there are many rows (>1M).

Add your own comment.