Name
CREATE INDEX
Synopsis
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEXindex
[USINGtype
|TYPEtype
] [USINGtype
|TYPEtype
] ONtable
(column
[(length
)] [ASC|DESC], . . . )
Use this statement to add an index to a
table after it has been created. This is an alias of the
ALTER TABLE
statement that adds an index. You can
add indexes only to MyISAM, InnoDB, and BDB types of tables. You can
also create these tables with indexes, as shown under the
CREATE TABLE
statement.
To prevent duplicates, add the
UNIQUE
flag between the CREATE
keyword and INDEX
. Only columns with
CHAR
, TEXT
, and
VARCHAR
datatypes of MyISAM tables can be indexed
with FULLTEXT
indexes. SPATIAL
indexes can index spatial columns only in MyISAM tables. This is
available starting with Version 4.1 of MySQL.
After the INDEX
keyword, the name of the index or
key is given. This name can be the same as one of the columns
indexed, or a totally new name.
You can specify the type of index with
the USING
keyword. For MyISAM and InnoDB tables,
BTREE
is the default and only choice currently.
The RTREE
type will be available as of Version 5
of MySQL. The TYPE
keyword is an alias for
USING
.
For wide columns, it may be advantageous to specify a maximum number of characters to use from a column for indexing. This can speed up indexing and reduce the size of index files on the filesystem.
Although
there is an ASC
option for sorting indexes in
ascending order and a DESC
option for sorting in descending order, these are for a future release of MySQL. ...
Get MySQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.