The same SQL execution plan is different for the master and slave databases

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 主从2个数据库同一个sql执行计划不一样

| username: 像风一样的男子

[TiDB Usage Environment] Production Environment / Testing / PoC
I have a simple SQL single table query that defaults to a full table scan without using the index in the primary database, but defaults to using the index when executed in the replica database. Why is this happening?


| username: tidb菜鸟一只 | Original post link

First, check if the primary database has both indexes. Also, collect the statistics of the primary database table using ANALYZE TABLE.

| username: 像风一样的男子 | Original post link

Both databases have indexes, and the analyze has been done several times with the same results.

| username: zhanggame1 | Original post link

Check if the numbers such as table_rows in the statistics from select * from INFORMATION_SCHEMA.TABLES on the primary and standby databases are roughly the same.

| username: 像风一样的男子 | Original post link

The data volume is the same, and the master-slave synchronization is normal.

| username: 胡杨树旁 | Original post link

Are the master and slave versions the same?

| username: 像风一样的男子 | Original post link

The same. The main question is why the primary database does not use the index.

| username: 胡杨树旁 | Original post link

Using EXPLAIN FORMAT='verbose' SELECT * FROM to see the cost evaluated by the optimizer.

| username: 像风一样的男子 | Original post link

Is a full table scan actually more efficient than using an index?

| username: cassblanca | Original post link

Execution plan cache? Try the SQL hint select /*+ ignore_plan_cache() */ * from t where a = ?;

| username: TiDB_C罗 | Original post link

Take a look at this 用 EXPLAIN 查看索引合并的 SQL 执行计划 | PingCAP 文档中心

| username: 像风一样的男子 | Original post link

Ignoring the cache query or not using the index

| username: 胡杨树旁 | Original post link

When using index_merge, check the cost. Compare the cost of both.

| username: 像风一样的男子 | Original post link

SET GLOBAL tidb_enable_index_merge = ON; Our database was upgraded from 4.0 to 5.4. By default, the index merge feature is not enabled. Now, after enabling it, that SQL query uses the index.

| username: zhanggame1 | Original post link

Are the table creation statements the same for the primary and standby databases? Check if they are all clustered or not.

| username: cy6301567 | Original post link

Are the table structure and statistics the same?

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.