MySQL 3.23, 4.0, 4.1 Reference Manual :: 12 SQL Statement Syntax :: 12.1 Data Definition Statements :: 12.1.4 CREATE INDEX Syntax

Section Navigation [Toggle]

12.1.4. `CREATE INDEX` Syntax

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

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

index_type:
    USING {BTREE | HASH}

In MySQL 3.22 or later, CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 12.1.2, “ALTER TABLE Syntax”. The CREATE INDEX statement does not do anything prior to MySQL 3.22. For more information about indexes, see Section 7.4.3, “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.5, “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 can 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. The NDBCLUSTER storage engine does not support prefixes (see Section 15.1.4.6, “Unsupported or Missing Features in MySQL Cluster”).

Prior to MySQL 4.1.2, the limit is 255 bytes for all storage engines supporting prefixes.

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. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

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. FULLTEXT indexes are available in MySQL 3.23.23 or later.

The MyISAM storage engine supports spatial columns such as (POINT and GEOMETRY. (Chapter 16, Spatial Extensions, describes the spatial data types.) Spatial and nonspatial indexes are available according to the following rules.

Characteristics of spatial indexes (created using SPATIAL INDEX):

Available only for MyISAM tables in MySQL 4.1 or later.
Indexed columns must be NOT NULL.
The full width of each column is indexed by default, but column prefix lengths are permitted. However, as of MySQL 5.0.40, the length is not displayed in SHOW CREATE TABLE output. mysqldump uses that statement. As of that version, if a table with SPATIAL indexes containing prefixed columns is dumped and reloaded, the index is created with no prefixes. (The full column width of each column is indexed.)

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

Permitted for MyISAM tables.
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 if you are using MySQL 3.23.2 or newer and are using the MyISAM, InnoDB, or BDB storage engine. This is also true for MEMORY tables as of MySQL 4.0.2. 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 storage engine, or MySQL 4.0.14 or newer and the 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.

From MySQL 4.1.0 on, 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 Engine	Permissible Index Types
`MyISAM`	`BTREE`
`InnoDB`	`BTREE`
`MEMORY`/`HEAP`	`HASH`, `BTREE`
`NDB` (MySQL 4.1.3 and later)	`HASH`, `BTREE` (see note in text)

Example:

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

Note

BTREE indexes are implemented by the NDBCLUSTER storage engine as T-tree indexes.

For indexes on NDBCLUSTER table columns, the USING clause can be specified only for a unique index or primary key. In such cases, the USING HASH clause prevents the creation of an implicit ordered index. Without USING HASH, a statement defining a unique index or primary key automatically results in the creation of a HASH index in addition to the ordered index, both of which index the same set of columns.

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.

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

Previous / Next / Up / Table of Contents

User Comments

Posted by Matthijs Lambooy on March 14 2006 9:38pm

[Delete] [Edit]

Only 16 fields are allowed in one fulltext index.

Posted by Andrew Krantz on April 27 2006 3:59am

[Delete] [Edit]

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 5:11pm

[Delete] [Edit]

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 12:24pm

[Delete] [Edit]

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 6:44pm

[Delete] [Edit]

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 3:39pm

[Delete] [Edit]

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 3:48pm

[Delete] [Edit]

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. :-/

Add your own comment.

Top / Previous / Next / Up / Table of Contents

12.1.4. CREATE INDEX Syntax

User Comments

12.1.4. `CREATE INDEX` Syntax