Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: tidb选错索引
Version: v6.1.0
Table structure:
Health status:
Strange execution plan:
Adding analyze yields the same result, see the attached image.
create table COPY_TABLE like CURRENT_TABLE;
insert into COPY_TABLE select * from CURRENT_TABLE. This works as expected with the index.
I want to know why it doesn’t choose to use the composite index that the where condition includes.
Why can’t it be forced to use this index? The MySQL client has already added -c, so ignoring comments is not an issue.
The type of ref_id is bigint. You can try changing the parameter to the data type.
This is not the reason, I have considered it. When there is a type mismatch, characters will be converted to numbers. This will not cause the index to become invalid.
Try running “EXPLAIN ANALYZE” to see the actual execution plan.
I mainly want to take a look at actrows.
pack_install_whb was created using create table like from the original table; then insert into the new table select * from the original table.
Have any of the statistics parameters been modified? For example, tidb_analyze_version.
Could you compare the SHOW STATS_HEALTHY of the two tables?
By the way, check if you have created SPM, which might cause the execution plan to be bound and not take effect.
How long is the GC set for? If there is too much old version data in the original table and the index correlation is relatively low, it will use the index with higher correlation. You can try cleaning up the GC and then try again.
Indeed, the execution plan was bound, as I took over the maintenance and didn’t consider this aspect.
Bound to the execution plan, right?
Hahaha, glad it’s resolved.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: 梦想是个厨子
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: