ADMIN CLEANUP INDEX
The ADMIN CLEANUP INDEX statement is used to delete redundant indexes from a table when the table has inconsistent data and index. Note that this syntax does not support foreign key constraints yet.
Synopsis
- AdminCleanupStmt
- TableNameList
AdminCleanupStmt ::=
'ADMIN' 'CLEANUP' ( 'INDEX' TableName IndexName | 'TABLE' 'LOCK' TableNameList )
TableNameList ::=
TableName ( ',' TableName )*
Examples
Assume that the tbl table in a database has inconsistent data and index due to some reasons (for example, some row data is lost in the cluster in a disaster recovery scenario):
SELECT * FROM tbl;
ERROR 1105 (HY000): inconsistent index idx handle count 3 isn't equal to value count 2
ADMIN CHECK INDEX tbl idx ;
ERROR 1105 (HY000): handle &kv.CommonHandle{encoded:[]uint8{0x1, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xf8}, colEndOffsets:[]uint16{0xa}}, index:types.Datum{k:0x5, decimal:0x0, length:0x0, i:0, collation:"utf8mb4_bin", b:[]uint8{0x0}, x:interface {}(nil)} != record:<nil>
It can be seen from the error message of the SELECT query that, the tbl table contains two rows of data and three rows of index data, which means inconsistent row and index data. At the same time, at least one index is in dangling state. In this case, you can use the ADMIN CLEANUP INDEX statement to delete the dangling index:
ADMIN CLEANUP INDEX tbl idx;
The execution result is as follows:
ADMIN CLEANUP INDEX tbl idx;
+---------------+
| REMOVED_COUNT |
+---------------+
| 1 |
+---------------+
You can execute the ADMIN CHECK INDEX statement again to check the consistency of data and index, and verify whether the data has been restored to a normal state:
ADMIN CHECK INDEX tbl idx;
Query OK, 0 rows affected (0.01 sec)
MySQL compatibility
This statement is a TiDB extension to MySQL syntax.