DROP INDEX
This statement removes an index from a specified table, marking space as free in TiKV.
Synopsis
- AlterTableDropIndexStmt
- AlterTableDropIndexSpec
AlterTableDropIndexStmt
::= 'ALTER' 'IGNORE'? 'TABLE' AlterTableDropIndexSpec ( ',' AlterTableDropIndexSpec )*
AlterTableDropIndexSpec
::= 'DROP' ( 'FOREIGN'? 'KEY' | 'INDEX' ) ( 'IF' 'EXISTS' )? Identifier
Examples
mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 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> CREATE INDEX c1 ON t1 (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> ALTER TABLE t1 DROP INDEX c1;
Query OK, 0 rows affected (0.30 sec)
MySQL compatibility
- Dropping the primary key of the
CLUSTERED
type is not supported. For more details about the primary key of theCLUSTERED
type, refer to clustered index.
See also
Was this page helpful?