(mysql.info) create-index
Info Catalog
(mysql.info) create-database
(mysql.info) data-definition
(mysql.info) create-table
13.1.4 `CREATE INDEX' Syntax
----------------------------
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX INDEX_NAME
[USING INDEX_TYPE]
ON TBL_NAME (INDEX_COL_NAME,...)
INDEX_COL_NAME:
COL_NAME [(LENGTH)] [ASC | DESC]
`CREATE INDEX' is mapped to an `ALTER TABLE' statement to create
indexes. See alter-table. For more information about how MySQL
uses indexes, see mysql-indexes.
Normally, you create all indexes on a table at the time the table
itself is created with `CREATE TABLE'. See create-table.
`CREATE INDEX' enables 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', `VARCHAR' `BINARY', and `VARBINARY' columns, indexes can be
created that use only part of a column, using `COL_NAME(LENGTH)' syntax
to specify an index prefix length. Index entries consist of the first
LENGTH characters of each column value for `CHAR' and `VARCHAR'
columns, and the first LENGTH bytes of each column value for `BINARY'
and `VARBINARY' columns. `BLOB' and `TEXT' columns also can be indexed,
but a prefix length _must_ be given.
The statement shown here creates an index using the first 10 characters
of the `name' column:
CREATE INDEX part_of_name ON customer (name(10));
If names in the column 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.
Prefixes can be up to 1000 bytes long (767 bytes for `InnoDB' tables).
Note that prefix limits are measured in bytes, whereas the prefix
length in `CREATE INDEX' statements is interpreted as number of
characters for non-binary data types (`CHAR', `VARCHAR', `TEXT'). Take
this into account when specifying a prefix length for a column that
uses a multi-byte character set.
In MySQL 5.0:
* You can add an index on a column that can have `NULL' values only
if you are using the `MyISAM', `InnoDB', `BDB', or `MEMORY' storage
engine.
* You can add an index on a `BLOB' or `TEXT' column only if you are
using the `MyISAM', `BDB', or `InnoDB' storage engine.
An INDEX_COL_NAME specification can end with `ASC' or `DESC'. These
keywords are allowed for future extensions for specifying ascending or
descending index value storage. Currently, they are parsed but ignored;
index values are always stored in ascending order.
Some storage engines allow you to specify an index type when creating
an index. The syntax for the INDEX_TYPE specifier is `USING TYPE_NAME'.
The allowable TYPE_NAME values supported by different storage engines
are shown in the following table. Where multiple index types are
listed, the first one is the default when no INDEX_TYPE specifier is
given.
*Storage *Allowable Index Types*
Engine*
`MyISAM' `BTREE'
`InnoDB' `BTREE'
`MEMORY'/`HEAP'`HASH', `BTREE'
Examples:
CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);
`TYPE TYPE_NAME' can be used as a synonym for `USING TYPE_NAME' to
specify an index type. However, `USING' is the preferred form. In
addition, the index name that precedes the index type in the index
specification syntax is not optional with `TYPE': Unlike `USING',
`TYPE' is not a reserved word and thus is interpreted as an index name.
If you specify an index type that is not legal for a given storage
engine, but there is another index type available that the engine can
use without affecting query results, the engine uses the available type.
`FULLTEXT' indexes are supported only for `MyISAM' tables and can
include only `CHAR', `VARCHAR', and `TEXT' columns. See
fulltext-search.
`SPATIAL' indexes are supported only for `MyISAM' tables and can
include only spatial columns that are defined as `NOT NULL'.
spatial-extensions, describes the spatial data types.
Info Catalog
(mysql.info) create-database
(mysql.info) data-definition
(mysql.info) create-table
automatically generated byinfo2html