GNU Info

(mysql.info)CREATE INDEX

---

Next: DROP INDEX Prev: DROP TABLE Up: Data Definition

---

Enter node , (file) or (file)node

`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.  Note: `ALTER TABLE'.


Normally, you create all indexes on a table at the time the table itself
is created with `CREATE TABLE'.  Note: `CREATE TABLE'.
`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.  (On `BLOB' and
`TEXT' columns the length is required). The statement shown below
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 Note: MySQL
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.  Note: Fulltext Search.