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:
AlterTableSpec:
ColumnKeywordOpt:
ColumnDef:
ColumnPosition:
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 | count | task | operator info |
+---------------------+----------+------+-------------------------------------------------------------+
| TableReader_7 | 10.00 | root | data:Selection_6 |
| └─Selection_6 | 10.00 | cop | eq(test.t1.c1, 3) |
| └─TableScan_5 | 10000.00 | cop | table:t1, range:[-inf,+inf], 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 | count | task | operator info |
+-------------------+-------+------+-----------------------------------------------------------------+
| IndexReader_6 | 10.00 | root | index:IndexScan_5 |
| └─IndexScan_5 | 10.00 | cop | table:t1, index:c1, range:[3,3], keep order:false, stats:pseudo |
+-------------------+-------+------+-----------------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL compatibility
FULLTEXT,HASHandSPATIALindexes are not supported.- 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-keyconfiguration item totrue. For details, see alter-primary-key.
See also
"ADD INDEX | TiDB SQL Statement Reference"
was last updated
Feb 21 2020:
release-3.1 branch: build (#1838) (be9d94a)