Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 【紧急】部分表建立索引查询数据有重复，主键查询是一条数据
[TiDB Usage Environment] Production Environment
[TiDB Version] 7.1.1
[Encountered Problem: Phenomenon and Impact]
After creating indexes on some tables, querying through the index returns duplicate results, while querying by primary key returns only one record. Deleting the index still results in duplicate queries.
Index Query
Primary Key Query
Even deleting and recreating the index doesn’t work.
Can the same effect be achieved by using SQL statements without tools?
ADMIN CHECK INDEX tbl_name idx_name;
Let’s check the consistency of the table and index.
Index issue reconstruction
The query results are also duplicated.
Deleting the table and re-importing it, then rebuilding the index, still yields the same result.
The execution result is fine.
Check the output of admin show ddl jobs to see if any of the indexes have the word “ingest”.
Indexes are created after importing the data.
We encountered this before as well. Create a new table, change the table name, and then import the data!
There is no other way except this method.
I didn’t solve it at the time, and I didn’t find a solution either.
Alright, I’ll give it a try. Thanks.
This is to rebuild the index again. If there are still issues, try setting the parameter tidb_ddl_enable_fast_reorg to false before rebuilding the index.
Yes, deleting and redoing still has issues. I’ll try again.
You haven’t done BR recovery, right? If you are restoring to a non-empty database using BR, you need to consider whether this is the issue.
|
username: TiDBer_hMqJwmpc
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: