Skip to Main Content
MySQL in a Nutshell, 2nd Edition
book

MySQL in a Nutshell, 2nd Edition

by Russell J. T. Dyer
April 2008
Intermediate to advanced content levelIntermediate to advanced
566 pages
21h 55m
English
O'Reilly Media, Inc.
Content preview from MySQL in a Nutshell, 2nd Edition

Name

CREATE INDEX

Synopsis

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index 
[USING type|TYPE type] 
[USING type|TYPE type] 
ON table (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 clause 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 in the CREATE TABLE statement later in this chapter.

To prevent duplicates, add the UNIQUE flag between the CREATE keyword and INDEX. Only columns with CHAR, TEXT, and VARCHAR data types of MyISAM tables can be indexed with FULLTEXT indexes.

Creating UNIQUE indexes

CREATE UNIQUE INDEX index
ON table (column, ...)

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, but RTREE is also available as of version 5.0 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. All indexes are currently sorted in ascending ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL in a Nutshell

MySQL in a Nutshell

Russell J. T. Dyer
MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
MySQL Cookbook

MySQL Cookbook

Paul DuBois

Publisher Resources

ISBN: 9780596514334Errata Page