Skip to content

Latest commit

 

History

History
120 lines (79 loc) · 4.51 KB

sql-statement-add-index.md

File metadata and controls

120 lines (79 loc) · 4.51 KB
Raw
title summary aliases
ADD INDEX | TiDB SQL Statement Reference
An overview of the usage of ADD INDEX for the TiDB database.
/docs/dev/sql-statements/sql-statement-add-index/
/docs/dev/reference/sql/statements/add-index/

ADD INDEX

The ALTER TABLE.. ADD INDEX statement adds an index to an existing table. This operation is online in TiDB, which means that neither reads or writes to the table are blocked by adding an index.

Synopsis

AlterTableStmt:

AlterTableStmt

AlterTableSpec:

AlterTableSpec

Constraint:

Constraint

ConstraintKeywordOpt:

ConstraintKeywordOpt

ConstraintElem:

ConstraintElem

IndexNameAndTypeOpt:

IndexNameAndTypeOpt

IndexPartSpecificationList:

IndexPartSpecificationList

IndexPartSpecification:

IndexPartSpecification

IndexOptionList:

IndexOptionList

IndexOption:

IndexOption

KeyOrIndex:

KeyOrIndex

IndexKeyTypeOpt:

IndexKeyTypeOpt

IndexInvisible:

IndexInvisible

IndexTypeName:

IndexTypeName

Examples

mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      |               | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.t1.c1, 3)              |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE t1 ADD INDEX (c1);
Query OK, 0 rows affected (0.30 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+------------------------+---------+-----------+------------------------+---------------------------------------------+
| id                     | estRows | task      | access object          | operator info                               |
+------------------------+---------+-----------+------------------------+---------------------------------------------+
| IndexReader_6          | 0.01    | root      |                        | index:IndexRangeScan_5                      |
| └─IndexRangeScan_5     | 0.01    | cop[tikv] | table:t1, index:c1(c1) | range:[3,3], keep order:false, stats:pseudo |
+------------------------+---------+-----------+------------------------+---------------------------------------------+
2 rows in set (0.00 sec)

MySQL compatibility

  • FULLTEXT, HASH and SPATIAL indexes are not supported.
  • VISIBLE/INVISIBLE index is not supported (currently only the master branch actually supports this feature).
  • Descending indexes are not supported (similar to MySQL 5.7).
  • Adding multiple indexes at the same time is currently not supported.
  • Adding the primary key constraint to a table is not supported by default. You can enable the feature by setting the alter-primary-key configuration item to true. For details, see alter-primary-key.

See also