(mysql.info.gz) CREATE INDEX

Info Catalog

(mysql.info.gz) CREATE DATABASE

(mysql.info.gz) Data Definition

(mysql.info.gz) CREATE TABLE

 
 13.2.5 `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]
 
 In MySQL 3.22 or later, `CREATE INDEX' is mapped to an `ALTER TABLE'
 statement to create indexes.   `ALTER TABLE' ALTER TABLE.  The
 `CREATE INDEX' statement doesn't do anything prior to MySQL 3.22.
 
 Normally, you create all indexes on a table at the time the table itself
 is created with `CREATE TABLE'.   `CREATE TABLE' 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 to index a prefix
 consisting of the first LENGTH characters of each column value. `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));
 
 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!
 
 Prefixes can be up to 255 bytes long (or 1000 bytes for `MyISAM' and
 `InnoDB' tables as of MySQL 4.1.2).  Note that prefix limits are
 measured in bytes, whereas the prefix length in `CREATE INDEX'
 statements is interpreted as number of characters.  Take this into
 account when specifying a prefix length for a column that uses a
 multi-byte character set.
 
 You can add an index on a column that can have `NULL' values only if
 you are using MySQL 3.23.2 or newer and are using the `MyISAM',
 `InnoDB', or `BDB' table type.  You can only add an index on a `BLOB'
 or `TEXT' column if you are using MySQL 3.23.2 or newer and are using
 the `MyISAM' or `BDB' table type, or MySQL 4.0.14 or newer and the
 `InnoDB' table type.
 
 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.
 
 From MySQL 4.1.0 on, 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'
 
 Example:
 
      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.  Also,
 the index name that precedes the index type in the index specification
 syntax is not optional with `TYPE'. This is because, 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 storage engine,
 but there is another index type available that the engine can use
 without affecting query results, the engine will use the available type.
 
 For more information about how MySQL uses indexes, see  MySQL
 indexes.
 
 `FULLTEXT' indexes can index only `CHAR', `VARCHAR', and `TEXT'
 columns, and only in `MyISAM' tables. `FULLTEXT' indexes are available
 in MySQL 3.23.23 or later.   Fulltext Search.
 
 `SPATIAL' indexes can index only spatial columns, and only in `MyISAM'
 tables. `SPATIAL' indexes are available in MySQL 4.1 or later. Spatial
 column types are described in  Spatial extensions in MySQL.
 

Info Catalog

(mysql.info.gz) CREATE DATABASE

(mysql.info.gz) Data Definition

(mysql.info.gz) CREATE TABLE