12.1.11. CREATE INDEX Syntax
Prev 12.1. Data Definition Statements Next

12.1.11. CREATE INDEX Syntax

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 12.1.6, “ALTER TABLE Syntax”. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead. For more information about indexes, see Section 7.3.1, “How MySQL Uses Indexes”.

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See Section 12.1.14, “CREATE TABLE Syntax”. 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.

Indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length:

  • Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY columns.

  • BLOB and TEXT columns also can be indexed, but a prefix length must be given.

  • Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. That is, index entries consist of the first length characters of each column value for CHAR, VARCHAR, and TEXT columns, and the first length bytes of each column value for BINARY, VARBINARY, and BLOB columns.

  • For spatial columns, prefix values cannot be given, as described later in this section.

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 column prefixes for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations.

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.

Note

Prefix limits are measured in bytes, whereas the prefix length in CREATE INDEX statements is interpreted as number of characters for nonbinary data types (CHAR, VARCHAR, TEXT). Take this into account when specifying a prefix length for a column that uses a multi-byte character set.

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

FULLTEXT indexes are supported only for MyISAM tables and can include only CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. See Section 11.9, “Full-Text Search Functions”, for details of operation.

The MyISAM, InnoDB, NDB, and ARCHIVE storage engines support spatial columns such as (POINT and GEOMETRY. (Section 11.17, “Spatial Extensions”, describes the spatial data types.) However, support for spatial column indexing varies among engines. Spatial and nonspatial indexes are available according to the following rules.

Characteristics of spatial indexes (created using SPATIAL INDEX):

  • Available only for MyISAM tables. Specifying SPATIAL INDEX for other storage engines results in an error.

  • Indexed columns must be NOT NULL.

  • In MySQL 5.5, column prefix lengths are prohibited. The full width of each column is indexed.

Characteristics of nonspatial indexes (created with INDEX, UNIQUE, or PRIMARY KEY):

  • Permitted for any storage engine that supports spatial columns except ARCHIVE.

  • Columns can be NULL unless the index is a primary key.

  • For each spatial column in a non-SPATIAL index except POINT columns, a column prefix length must be specified. (This is the same requirement as for indexed BLOB columns.) The prefix length is given in bytes.

  • The index type for a non-SPATIAL index depends on the storage engine. Currently, B-tree is used.

In MySQL 5.5:

  • You can add an index on a column that can have NULL values only if you are using the MyISAM, InnoDB, or MEMORY storage engine.

  • You can add an index on a BLOB or TEXT column only if you are using the MyISAM, or InnoDB storage engine.

An index_col_name specification can end with ASC or DESC. These keywords are permitted 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.

Following the index column list, index options can be given. An index_option value can be any of the following:

  • KEY_BLOCK_SIZE [=] value

    This option provides a hint to the storage engine about the size in bytes to use for index key blocks. The engine is permitted to change the value if necessary. A value of 0 indicates that the default value should be used.

  • index_type

    Some storage engines permit you to specify an index type when creating an index. The permissible index type 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 EnginePermissible Index Types
    MyISAMBTREE
    InnoDBBTREE
    MEMORY/HEAPHASH, BTREE
    NDBHASH, BTREE (see note in text)

    Example: 

    CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);

    The index_type clause cannot be used together with SPATIAL INDEX.

    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. The parser recognizes RTREE as a type name, but currently this cannot be specfied for any storage engine.

    Use of this option before the ON tbl_name clause is deprecated; support for use of the option in this position is to be dropped in a future MySQL release. If an index_type option is given in both the earlier and later positions, the final option applies.

    TYPE type_name is recognized as a synonym for USING type_name. However, USING is the preferred form.

  • WITH PARSER parser_name

    This option can be used only with FULLTEXT indexes. It associates a parser plugin with the index if full-text indexing and searching operations need special handling. See Section 23.2, “The MySQL Plugin API”, for details on creating plugins.

  • As of MySQL 5.5.3, index definitions can include an optional comment of up to 1024 characters.

Copyright © 1997, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices
Prev Up Next
12.1.10. The CREATE FUNCTION Statement Home 12.1.12. CREATE PROCEDURE and CREATE FUNCTION Syntax
Copyright © 2010-2024 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout