DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(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