CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEXindex_name
[index_type
] ONtbl_name
(index_col_name
,...) [index_option
] ...index_col_name
:col_name
[(length
)] [ASC | DESC]index_type
: USING {BTREE | HASH | RTREE}index_option
: KEY_BLOCK_SIZE [=]value
|index_type
| WITH PARSERparser_name
CREATE INDEX
is mapped to an
ALTER TABLE
statement to create
indexes. See Section 12.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 7.4.4, “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.17, “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
syntax to specify an index prefix length:
col_name
(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. The
NDBCLUSTER
storage engine does not support
prefixes (see
Section 17.1.5.6, “Unsupported or Missing Features in MySQL Cluster”).
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.
Beginning with MySQL 5.1.7, indexes on variable-width columns are
created online; that is, creating the indexes does not require any
copying of the table. For NDBCLUSTER
tables, the table is not locked against access from other MySQL
Cluster API nodes, although it is locked against other operations
on the same API node for the duration of the
online operation. This is done automatically by the server
whenever it determines that it is possible to do so; you do not
have to use any special SQL syntax or server options to cause it
to happen.
In standard MySQL 5.1 releases, it is not possible to
override the server when it determines that an index is to be
created online. In MySQL Cluster, beginning with MySQL Cluster NDB
6.2.5 and MySQL Cluster NDB 6.3.3, you can create indexes offline
(which causes the table to be locked to all API nodes in the
cluster) using the OFFLINE
keyword. The rules
and limitations governing online CREATE OFFLINE
INDEX
and CREATE ONLINE INDEX
are the
same as for ALTER OFFLINE TABLE ... ADD INDEX
and ALTER ONLINE TABLE ... ADD INDEX
. You
cannot cause the online creation of an index that would normally
be created offline by using the ONLINE
keyword
(if it is not possible to perform the CREATE
INDEX
operation online, then the
ONLINE
keyword is ignored). For more
information, see Section 12.1.7, “ALTER TABLE
Syntax”.
The ONLINE
and OFFLINE
keywords are available only in MySQL Cluster NDB 6.2 and MySQL
Cluster NDB 6.3 releases beginning with versions 6.2.5 and
6.3.3, respectively; attempting to use them in earlier MySQL
Cluster NDB 6.2 or 6.3 releases, standard MySQL 5.1 releases, or
MySQL Cluster NDB 6.1 releases results in a syntax error.
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 allows
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.
MySQL Enterprise. Lack of proper indexes can greatly reduce performance. Subscribe to the MySQL Enterprise Monitor for notification of inefficient use of indexes. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
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.8, “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.13, “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.
Spatial indexes (created using SPATIAL INDEX
):
Available only for MyISAM
tables.
Specifying a SPATIAL INDEX
for other
storage engines results in an error.
Indexed columns must be NOT NULL
.
In MySQL 5.1, column prefix lengths are prohibited. The full width of each column is indexed.
Nonspatial indexes (created with INDEX
,
UNIQUE
, or PRIMARY KEY
):
Allowed 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.1:
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.
As of MySQL 5.1.10, index options can be given following the index
column list. 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 allowed to change the value if necessary. A value of 0 indicates that the default value should be used.
index_type
Some storage engines allow you to specify an index type when creating an index. The allowable 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 | Allowable Index Types |
MyISAM |
BTREE , RTREE
|
InnoDB |
BTREE |
MEMORY /HEAP
|
HASH , BTREE
|
NDB |
HASH , BTREE (see note in text) |
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 RTREE
index type is allowable only for
SPATIAL
indexes.
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.
Examples:
CREATE TABLE lookup (id INT) ENGINE = MEMORY; CREATE INDEX id_index USING BTREE ON lookup (id);
TYPE
is recognized as a synonym for type_name
USING
. However,
type_name
USING
is the preferred form.
Before MySQL 5.1.10, this option can be given only before the
ON
clause. Use of the option in this position is deprecated as of
5.1.10; support for it is to be dropped in a future MySQL
release. If an tbl_name
index_type
option is given
in both the earlier and later positions, the final option
applies.
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 22.2, “The MySQL Plugin API”, for details on
creating plugins.
User Comments
Only 16 fields are allowed in one fulltext index.
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).
From my experience, adding an index to a table locks the table for reads as well as writes.
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.
You can force a new unique index to drop duplicate rows, but if you just do it the normal way you get an error:
ERROR 1062 (23000): Duplicate entry '1277991-1-text-text-ext ' for key 2
Instead, do:
ALTER IGNORE TABLE `table` ADD UNIQUE INDEX `name` (`one_id`, `two_id`, `content`(64));
The server will respond with:
Query OK, 40003 rows affected (10.09 sec)
Records: 40003 Duplicates: 234 Warnings: 0
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');
Add your own comment.