ADMIN RECOVER INDEX
When the row data and index data are inconsistent, you can use the ADMIN RECOVER INDEX statement to recover the consistency based on the redundant indexes. Note that this syntax does not support foreign key constraints yet.
Synopsis
- AdminCleanupStmt
AdminCleanupStmt ::=
'ADMIN' 'RECOVER' 'INDEX' TableName IndexName
Examples
Assume that the tbl table in a database has inconsistent row 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 2 isn't equal to value count 3
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 three rows of data and two rows of index data, which means inconsistent row data and index data. At the same time, at least one row of data has no corresponding index. In this case, you can use the ADMIN RECOVER INDEX statement to supplement the missing index:
ADMIN RECOVER INDEX tbl idx;
The execution result is as follows:
ADMIN RECOVER INDEX tbl idx;
+-------------+------------+
| ADDED_COUNT | SCAN_COUNT |
+-------------+------------+
| 1 | 3 |
+-------------+------------+
1 row in set (0.00 sec)
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 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.