Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 32.9Mb
PDF (A4) - 32.9Mb
PDF (RPM) - 30.9Mb
HTML Download (TGZ) - 7.8Mb
HTML Download (Zip) - 7.9Mb
HTML Download (RPM) - 6.7Mb
Man Pages (TGZ) - 143.5Kb
Man Pages (Zip) - 203.6Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


Pre-General Availability Draft: 2017-07-21

13.1.12 CREATE INDEX Syntax

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

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

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 13.1.7, “ALTER TABLE Syntax”. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead. For more information about indexes, see Section 8.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 13.1.15, “CREATE TABLE Syntax”. This guideline is especially important for InnoDB tables, where the primary key determines the physical layout of rows in the data file. CREATE INDEX enables you to add indexes to existing tables.

A column list of the form (col1, col2, ...) creates a multiple-column index. Index key values are formed by concatenating the values of the given columns.

For string 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 column indexes.

  • Prefixes must be specified for BLOB and TEXT column indexes.

  • Prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements is interpreted as number of characters for nonbinary string types (CHAR, VARCHAR, TEXT) and number of bytes for binary string types (BINARY, VARBINARY, BLOB). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set.

  • 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 (assuming that name has a nonbinary string type):

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 767 bytes long for InnoDB tables that use the REDUNDANT or COMPACT row format. The length limit is raised to 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. For MyISAM tables, the prefix limit is 1000 bytes.

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 InnoDB and 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 12.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.5, “Extensions for Spatial Data”, 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.

Spatial indexes (created using SPATIAL INDEX) have these characteristics:

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

  • Indexed columns must be NOT NULL.

  • 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.

  • You can add an index on a column that can have NULL values only for InnoDB, MyISAM, and MEMORY tables.

  • You can add an index on a BLOB or TEXT column only for using the InnoDB and MyISAM tables.

  • When the innodb_stats_persistent setting is enabled, run the ANALYZE TABLE statement for an InnoDB table after creating an index on that table.

InnoDB supports secondary indexes on virtual columns. For more information, see Section 13.1.15.9, “Secondary Indexes and Generated Columns”.

An index_col_name specification can end with ASC or DESC to specify whether index values are stored in ascending or descending order. The default is ascending if no order specifier is given. ASC and DESC are not permitted for HASH indexes.

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

  • KEY_BLOCK_SIZE [=] value

    For MyISAM tables, KEY_BLOCK_SIZE optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. A KEY_BLOCK_SIZE value specified for an individual index definition overrides a table-level KEY_BLOCK_SIZE value.

    KEY_BLOCK_SIZE is not supported at the index level for InnoDB tables. See Section 13.1.15, “CREATE TABLE Syntax”.

  • index_type

    Some storage engines permit you to specify an index type when creating an index. For example:

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

    Table 13.1, “Index Types Per Storage Engine” shows the permissible index type values supported by different storage engines. Where multiple index types are listed, the first one is the default when no index type specifier is given. Storage engines not listed in the table do not support an index_type clause in index definitions.

    Table 13.1 Index Types Per Storage Engine

    Storage EnginePermissible Index Types
    InnoDBBTREE
    MyISAMBTREE
    MEMORY/HEAPHASH, BTREE
    NDBHASH, BTREE (see note in text)

    The index_type clause cannot be used for FULLTEXT INDEX or SPATIAL INDEX specifications. Full-text index implementation is storage engine dependent. Spatial indexes are implemented as R-tree indexes.

    If you specify an index type that is not valid for a given storage engine, but another index type is 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 specified for any storage engine.

    Note

    Use of the index_type option before the ON tbl_name clause is deprecated; support for use of the option in this position will be removed 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.

    For the storage engines that support an index_type option, Table 13.2, “Storage Engine Index Characteristics” shows some characteristics of index use.

    Table 13.2 Storage Engine Index Characteristics

    Storage EngineIndex TypeIndex ClassStores NULL ValuesPermits Multiple NULL ValuesIS NULL Scan TypeIS NOT NULL Scan Type
    InnoDBBTREEPrimary keyNoNoN/AN/A
    UniqueYesYesIndexIndex
    KeyYesYesIndexIndex
    InapplicableFULLTEXTYesYesTableTable
    InapplicableSPATIALNoNoN/AN/A
    MyISAMBTREEPrimary keyNoNoN/AN/A
    UniqueYesYesIndexIndex
    KeyYesYesIndexIndex
    InapplicableFULLTEXTYesYesTableTable
    InapplicableSPATIALNoNoN/AN/A
    MEMORYHASHPrimary keyNoNoN/AN/A
    UniqueYesYesIndexIndex
    KeyYesYesIndexIndex
    BTREEPrimaryNoNoN/AN/A
    UniqueYesYesIndexIndex
    KeyYesYesIndexIndex

  • 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. InnoDB and MyISAM support full-text parser plugins. See Full-Text Parser Plugins and Section 28.2.4.4, “Writing Full-Text Parser Plugins” for more information.

  • COMMENT 'string'

    Index definitions can include an optional comment of up to 1024 characters.

    The MERGE_THRESHOLD for index pages can be configured for individual indexes using the index_option COMMENT clause of the CREATE INDEX statement. For example:

    CREATE TABLE t1 (id INT);
    CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

    If the page-full percentage for an index page falls below the MERGE_THRESHOLD value when a row is deleted or when a row is shortened by an update operation, InnoDB attempts to merge the index page with a neighboring index page. The default MERGE_THRESHOLD value is 50, which is the previously hardcoded value.

    MERGE_THRESHOLD can also be defined at the index level and table level using CREATE TABLE and ALTER TABLE statements. For more information, see Section 15.6.12, “Configuring the Merge Threshold for Index Pages”.

  • VISIBLE, INVISIBLE

    Modify index visibility. Indexes are visible by default. An invisible index is not used by the optimizer. Modification of index visibility applies to indexes other than primary keys (either explicit or implicit). This feature is storage engine neutral (supported for any engine). For more information, see Section 8.3.11, “Invisible Indexes”.

ALGORITHM and LOCK clauses may be given to influence the table copying method and level of concurrency for reading and writing the table while its indexes are being modified. They have the same meaning as for the ALTER TABLE statement. For more information, see Section 13.1.7, “ALTER TABLE Syntax”


User Comments
  Posted by Matthijs Lambooy on March 14, 2006
Only 16 fields are allowed in one fulltext index.
  Posted by Andrew Krantz on April 27, 2006
Be careful when you run this because MySQL will LOCK the table for WRITES during the index creation. Building the index can take a while on large tables even if the column is empty (all nulls).
  Posted by Nathan Moon on May 11, 2007
From my experience, adding an index to a table locks the table for reads as well as writes.
  Posted by Varun Grover on September 28, 2007
Running SELECTs on a table on which an index is being created may block because the server may need to use the index for looking-up records; and, the index is locked because it is being written to.
  Posted by David OBrien on March 26, 2009
Since there is no
CREATE INDEX IF NOT EXISTS

We made this sproc to do it...

--------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS `create_index_if_not_exists`$$

CREATE DEFINER=`user`@`%` PROCEDURE `create_index_if_not_exists`(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(200))
SQL SECURITY INVOKER
BEGIN

set @Index_cnt = (
select count(1) cnt
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = table_name_vc
and index_name = index_name_vc
);

IF ifnull(@Index_cnt,0) = 0 THEN set @index_sql = concat('Alter table ',table_name_vc,' ADD INDEX ',index_name_vc,'(',field_list_vc,');');

PREPARE stmt FROM @index_sql;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END IF;

END$$
DELIMITER ;
------------------------

use it like...

call create_index_if_not_exists('tablename','indexname','thisfield,thatfield');
  Posted by Lincoln Rickwood on June 24, 2011
Here's an alternative stored procedure (Sorry about the lack of indentation, the comment box seems to strip leading spaces)...

delimiter //
drop procedure if exists AddTableIndex //
create procedure AddTableIndex
( in schemaName varchar(128) -- If null use name of current schema;
, in tableName varchar(128) -- If null an exception will be thrown.
, in indexName varchar(128) -- If null an exception will be thrown.
, in indexDefinition varchar(1024) -- E.g. '(expireTS_ ASC)'
, in ifPresent enum('leaveUnchanged', 'dropAndReplace') -- null=leaveUnchanged.
, out outcome tinyint(1) -- 0=unchanged, 1=replaced, 4=added.
)
begin
declare doDrop tinyint(1) default null;
declare doAdd tinyint(1) default null;
declare tmpSql varchar(4096) default '';

set schemaName = coalesce(schemaName, schema());
set ifPresent = coalesce(ifPresent, 'leaveUnchanged');
if exists
(SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = schemaName
AND table_name = tableName
AND index_name = indexName
)
then
if (ifPresent = 'leaveUnchanged')
then
set doDrop = 0;
set doAdd = 0;
set outcome = 0;
elseif (ifPresent = 'dropAndReplace')
then
set doDrop = 1;
set doAdd = 1;
set outcome = 1;
end if;
else
set doDrop = 0;
set doAdd = 1;
set outcome = 4;
end if;

if (doDrop = 1)
then
set tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` drop index `', indexName, '` ');
set @sql = tmpSql;
prepare tmp_stmt from @sql;
execute tmp_stmt;
deallocate prepare tmp_stmt;
end if;

if (doAdd = 1)
then
set tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` add index `', indexName, '` ', indexDefinition);
set @sql = tmpSql;
prepare tmp_stmt from @sql;
execute tmp_stmt;
deallocate prepare tmp_stmt;
end if;

end; //

  Posted by James Greene on May 3, 2013
OK, so I'm a bit green myself when it comes to optimizing MySQL. So, I'm giving this little bit of advice to the other newbs out there:

Indexing a LARGE amount of data, this can take hours. Thankfully, I used on a test database. Please be careful, if you plan on adding index on a production database. :-/
Sign Up Login You must be logged in to post a comment.