Bug #112945 The problem of large execution speed difference between two mysql slave
Submitted: 3 Nov 2023 1:34 Modified: 5 Nov 2023 12:42
Reporter: harry harry Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.34 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[3 Nov 2023 1:34] harry harry 
Description:
In our mysql master-slave replication, the mysql master port number is 3306, and it has two slave with port numbers 3011 and 3012 respectively.
There is the following SQL, which is executed on 3011 and 3012 respectively. The execution plans are very different, resulting in a large difference in the execution time of the SQL on the two slave instances:

How to repeat:
SELECT m0.NumericalOrder

FROM
         nxin_qlw_zlw.ms_transferhoursedetail
m
        INNER  
        JOIN nxin_qlw_zlw.ms_transferhourse m0 ON m.NumericalOrder = m0.NumericalOrder
        AND m.IsIn = m0.IsIn
        AND m.Abstract = m0.Abstract
        INNER JOIN nxin_qlw_business.wm_warehousestockpigextend w ON m.NumericalOrderDetail = w.NumericalOrderDetail
WHERE
m0.DataDate BETWEEN '2023-10-01' AND '2023-10 -25'
        AND FIND_IN_SET( m0.EnterpriseID, '2793862' )
    AND FIND_IN_SET( m0.PigFarmID,'2204141338210000077,2203030933030000077' )

The running time on the 3011 instance is 0.2s
The running time on the 3012 instance is 15s
See the execution time big difference.

3011 instance execution plan:
+----+-------------+-------+------------+---- ---+------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------+-------------- --------------------------------------------------+-------- -+----------------------------------------+--------+- ----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------
+ ------------+-------+-------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --+-------------------------------------------------- --------+---------+--------------------------------- ------+--------+----------+----------------------- -------------+
| 1 | SIMPLE | m0 | NULL | range | idx_NumericalOrder,idx_DataDate,idx_NumericalOrder_Abstract_IsIn | idx_DataDate | 3 | NULL | 265148 | 100.00 | Using index condition; Using where |
| 1 | SIMPLE | m | NULL | ref | idx_NumericalOrder,idx_NumericalOrder_PigID,idx_NumericalOrder_BatchID_PigID,idx_NumericalOrder_PigHouseUnitID_PigID,idx_NumericalOrderDetail_IsIn,idx_NumericalOrder_IsIn_Abstract | idx_Numer icalOrder | 8 | nxin_qlw_zlw.m0.NumericalOrder | 7 | 5.00 | Using where | | 1 | SIMPLE | w | NULL |
ref | wm_warehousestockpigextend_NumericalOrderDetail_index | wm_warehousestockpigextend_NumericalOrderDetail_index | 8 | nxin_qlw_zlw.m.NumericalOrderDetail | 1 | 100.00 | Using index |
+----+-------------+--------+-- ----------+-------+---------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- +------------------------------------------------- ------+---------+---------------------------------- ----+--------+----------+-------------------------- ----------+ 3012 instance execution plan: +----+-------------+-------+----- -------+-------+---------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------+-- -------------------------------------------------- ---+---------+---------------------------------------- ---------------------------------------+---------+ ----------+----------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------- +---------------------+-------+---------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---+------------------------------------------------- ---------+---------+--------------------------------- ----------------------------------------+---- -----+----------+---------------------------------- ---+

| 1 | SIMPLE | w | NULL | index | wm_warehousestockpigextend_NumericalOrderDetail_index | wm_warehousestockpigextend_NumericalOrderDetail_index | 8 | NULL | 1027880 | 100.00 | Using index | PigID,idx_NumericalOrder_BatchID_PigID,idx_NumericalOrder_PigHouseUnitID_PigID,idx_NumericalOrderDetail_IsIn,idx_NumericalOrder_IsIn_Abstract |
idx_NumericalOrderDetail_IsIn | 8 | w.NumericalOrderDetail | 1 | 100.00 | Using index condition;
Using where | tract,m.IsIn | 1 | 9.67 | Using where |
+----+-------------+-------+------------+- ------+------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------+---------- --------------------------------------------------+----- ----+------------------------------------------------ -------------------------------+---------+--------- -+----------------------------------------+

The number of table rows in the two instances is the same, Because it is master-slave replication:
mysql > select count(*) from ms_transferhoursedetail m;
+----------+
| count(*) |
+----------+
| 24032442 |
+----------+
1 row in set (14.74 sec)

mysql> select count(*) from ms_transferhourse m0;
+----------+
| count(*) |
+ ----------+
| 2800958 |
+----------+
1 row in set (1.53 sec)

mysql> select count(*) from wm_warehousestockpigextend w;
+---- ------+
| count(*) |
+----------+
| 1020151 |
+----------+
1 row in set (0.29 sec)

Database version It’s the same, both mysql 5.7.34

table structures are the same :
mysql> show create table ms_transferhoursedetail m \G
************************ *** 1. row ***************************
       Table: ms_transferhoursedetail
Create Table: CREATE TABLE `ms_transferhoursedetail` (
  `RecordID` bi gint (20) NOT NULL AUTO_INCREMENT,
  `NumericalOrderDetail` bigint(20) NOT NULL DEFAULT '0' ,
  `NumericalOrder` bigint(20) NOT NULL DEFAULT '0' ,
  `Abstract` bigint(20) DEFAULT '0' ,
  `IsIn` bit(1) DEFAULT b'1' ,
  `BatchID` bigint(20) NOT NULL DEFAULT ' 0',
  `PigID` bigint(20) NOT NULL DEFAULT '0' ,
  `PigHouseUnitID` bigint(20) NOT NULL DEFAULT '0' ,
  `PigField` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '' ,
  `PigCount` int (11) NOT NULL DEFAULT '0' ,
  `PigTotalWeight` decimal(18,2) NOT NULL DEFAULT '0.00' ,
  `PigCost` decimal(18,4) NOT NULL DEFAULT '0.0000' ,
  `Remarks` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '' ,
  `PigBatchType` bigint(20) NOT NULL DEFAULT '0' ,
  `Sort` int(11) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`RecordID`),
  KEY `idx_NumericalOrder` (`NumericalOrder `),
  KEY `idx_NumericalOrder_PigID` (`NumericalOrder`,`PigID`),
  KEY `idx_NumericalOrder_BatchID_PigID` (`NumericalOrder`,`BatchID`,`PigID`),
  KEY `idx_NumericalOrder_PigHouseUnitID_PigID` (`NumericalOrder`,`P igHouseUnitID`,`PigID `),
  KEY `idx_PigID_Abstract` (`PigID`,`Abstract`),
  KEY `idx_BatchID_IsIn` (`BatchID`,`IsIn`) USING BTREE,
  KEY `idx_NumericalOrderDetail_IsIn` (`NumericalOrderDetail`,`IsIn`) USING BTREE,
  KEY `idx_NumericalOrder_IsIn_Abstract` (`NumericalOrder`,`IsIn`,`Abstract`)
) ENGINE=InnoDB

mysql> show create table ms_transferhourse m0 \G
********************* ****** 1. row ***************************
       Table: ms_transferhourse
Create Table: CREATE TABLE `ms_transferhourse` (
  `RecordID ` bigint(20) NOT NULL AUTO_INCREMENT ,
  `PigFarmID` bigint(20) NOT NULL DEFAULT '0' ,
  `EnterpriseID` bigint(20) NOT NULL DEFAULT '0' ,
  `NumericalOrder` bigint(20) NOT NULL DEFAULT '0' ,
  `Number` bigint(20) NOT NULL DEFAULT '0' ,
  `DataDate` date NOT NULL,
  `PigType` bigint(20) NOT NULL DEFAULT '0' ,
  `Abstract` bigint(20) NOT NULL DEFAULT '0' ,
  `IsIn` bit(1) NOT NULL DEFAULT b'1' ,
  `PersonID` bigint(20) NOT NULL DEFAULT '0' ,
  `OwnerID` bigint(20) NOT NULL DEFAULT '0' ,
  `CreatedOwnerID` bigint(20) NOT NULL DEFAULT '0' ,
  `CreatedDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `ModifiedDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  `Remarks` varchar(512) NOT NULL DEFAULT '' ,
  PRIMARY KEY (`RecordID`),
  KEY `idx_NumericalOrder` (`NumericalOrder`),
  KEY `idx_EnterpriseID` (`EnterpriseID`),
  KEY `idx_PigType` (`PigType`),
  KEY `idx_DataDate` (`DataDate`),
  KEY `idx_NumericalOrder_Abstract_IsIn` (`NumericalOrder`,`Abstract`,`IsIn`),
  KEY `idx_PigFarmID_DataDate_PigType` (`PigFarmID`,`DataDate`,`PigType`) USING BTREE,
  KEY `idx_PigFarmID_PigType_Abstract_DataDate` (`Pig FarmID`,`PigType` ,`Abstract`,`DataDate`) USING BTREE
) ENGINE=InnoDB
1 row in set (0.00 sec)

mysql> show create table wm_warehousestockpigextend w \G
****************** ********* 1. row ***************************
       Table: wm_warehousestockpigextend
Create Table: CREATE TABLE `wm_warehousestockpigextend` (
  `RecordID` int(20) NOT NULL AUTO_INCREMENT ,
  `NumericalOrder` bigint(20) NOT NULL ,
  `Guid` char(36) NOT NULL ,
  `PigID` bigint(20) NOT NULL ,
  `NumericalOrderDetail` bigint(20) NOT NULL ,
  PRIMARY KEY (`RecordID`),
  KEY `wm_warehousestockpigextend_NumericalOrderDetail_index` (`NumericalOrderDetail`),
  KEY `wm_warehousestockpigextend_NumericalOrder_index` (`NumericalOrder`),
  KEY `wm_warehousestockpigextend_NumericalOrder_Guid_index` (`Num ericalOrder`,`Guid`),
  KEY `idx_Guid` (` Guid`)
) ENGINE=InnoDB
1 row in set (0.00 sec)

The statistical information is similar, and the statistical information has been manually re-collected:
3011 instance index:
mysql> show index from ms_transferhoursedetail m;
+---------- ----------------+----------------+------------------------ --------------------------+-------------+------------- ---------+-----------+-------------+----------+--- -----+------+------------+---------+------------- -+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------- -----+------------+---------------------------------- ----------+--------------+---------------------+- ----------+-------------+----------+--------+----- -+----------------+---------+---------------+
| ms_transferhoursedetail | 0 | PRIMARY | 1 | RecordID | A | 23435062 | NULL | NULL | | BTREE | | |
| ms_transferhoursedetail | 1 | idx_NumericalOrder |
1 | NumericalOrder | alOrder | A | 1968168 | NULL | NULL | | BTREE | | |
| ms_transferhoursedetail | 1 | idx_NumericalOrder_PigID | 2 | PigID | A | 13410846 | NULL |
NULL | | BTREE | | | | ms_transferhoursedetail | 1 | idx_NumericalOrder_BatchID_PigID | BTREE | | | |
ms_transferhoursedetail | 1 | idx_NumericalOrder_BatchID_PigID | 2 | BatchID | A | 2985620
| NULL | NULL | | BTREE | | | | ms_transferhoursedetail | 1 | idx_NumericalOrder_BatchID_PigID | 3 | _transferhoursedetail
| 1 | idx_NumericalOrder_PigHouseUnitID_PigID | 1 | NumericalOrder | A | 2281140 | NULL | NULL | | BTREE | | | |
ms_transferhoursedetail | 1 | idx_NumericalOrder_PigHouseUnitID_PigID | 2 | PigHouseUnitID | | BTREE | | | | ms_transferhoursedetail | 1 |
idx_NumericalOrder_PigHouseUnitID_PigID | 3 | PigID | A | 20047188 | NULL | NULL | | BTREE | | | | ms_transferhoursedetail | 1 | idx_PigID_Abstract | 1 | PigID | A |
5141371 | NULL | NULL |
| Abstract | A | 10935326 | NULL | NULL | YES | BTREE | | | | ms_transferhoursedetail | 1 | idx_BatchID_IsIn | 1 | BatchID | A
| 155468 | NULL |
IsIn | A | 279008 | NULL | NULL | YES | BTREE | | | |
ms_transferhoursedetail | 1 | idx_NumericalOrderDetail_IsIn | 1
| NumericalOrderDetail | Detail_IsIn | 2 | IsIn | A | 23471338 | NULL | NULL | YES | BTREE | | |
| ms_transferhoursedetail | 1 | idx_NumericalOrder_IsIn_Abstract | 1 | NumericalOrder | A | 2092309 | NULL | NULL | | BTREE | | | | ms_transferhoursedetail | 1 | idx_NumericalOrder_IsIn_Abstract | 2 | NULL | YES | BTREE | |
|
| ms_transferhoursedetail | 1 | idx_NumericalOrder_IsIn_Abstract | 3 | Abstract | A | 2937906 | NULL | NULL | YES | BTREE | | |
+-------------------------- +------------+------------------------------------ -----+--------------+----------------------+------ -----+-------------+----------+--------+------+--- ---------+---------+---------------+
19 rows in set (0.00 sec)

mysql> show index from ms_transferhourse m0;
+--------------------------+---------------------+-------------- --------------------------+-------------+------- ---------+-----------+-------------+----------+--- -----+------+------------+---------+------------- -+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+ ------------+--------------------------------------------- ----+--------------+----------------+-----------+- ------------+----------+--------+------+---------- --+---------+---------------+
| ms_transferhourse | 0 | PRIMARY | 1 | RecordID | A | 2770010 | NULL | NULL | | BTREE | | |
| ms_transferhourse | 1 | idx_NumericalOrder | 1 | NumericalOrder | A | 1956099 | NULL | NULL | | BTREE
| |
| ms_transferhourse | 1 | idx_PigType | 1 | PigType | A | 2 | NULL | NULL | | BTREE | | | | ms_transferhourse | 1 | idx_DataDate |
1 | DataDate | A | 1639 | NULL | NULL | | BTREE |
| ms_transferhourse | 1 | idx_NumericalOrder_Abstract_IsIn | 1 | NumericalOrder | A | 2053100 | NULL | NULL | | BTREE | | |
| ms_transferhourse | 1 | idx_NumericalOrder_Abstract_IsIn | 2 | Abstract | A | 2175299 | NULL | NULL | | BTREE | | | | ms_transferhourse | 1 | idx_NumericalOrder_Abstract_IsIn | 3 |
|
ms_transferhourse | 1 | IDX_PIGFARMIMIMIMID_DATADATE_PIGTYPE | 1 | Pigfarmid | A | 2571 | NULL | BTREE | | | |
Ms_TRANSFERHOURSE | 1 | IDX_PIGFARMID_PIGTYPE | 2 | DataDate | A | 453953 | NULL | NULL | | BTREE | | | | MS_TRANSFERSE
| 1 | idx_PigFarmID_DataDate_PigType | 3 | PigType | A | 558506 | NULL |
NULL | | BTREE | | | | ms_transferhourse |
1 | idx_PigFarmID_PigType_Abstract_DataDate | 1 | PigFarmID | | idx_PigFarmID_PigType_Abstract_DataDate | 2 | PigType | A | 6962 | NULL | NULL | | BTREE | |
| | ms_transferhourse |
1 | idx_PigFarmID_PigType_Abstract_DataDate | 3 | Abstract | | 4 | DataDate | A | 1395488 | NULL | NULL | | BTREE | | |
+-------------------+------------+ --------------------------------------------------+-------- ------+----------------+----------+-------------+ ----------+--------+------+----------------+---------+ ---------------+
15 rows in set (0.00 sec)

mysql> show index from wm_warehousestockpigextend w;
+----------------- ----------+----------------+------------------------- -------------------------------+--------------+---- ------------------+----------+-------------+----- -----+--------+------+------------+---------+----- ----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------- ------------------+---------------------+------------------ ----------------------------------+------------- -+----------------------+----------+------------- +----------+--------+------+------------+--------- +-------------+

| wm_warehousestockpigextend | 0 | PRIMARY | 1 | RecordID | A | 1002004 | NULL | NULL | | BTREE | | | | wm_warehousestockpigextend | 1 | wm_warehousestockpigextend_NumericalOrderDetail_index |
wm_warehousestockpigextend | 1 | wm_warehousestockpigextend_NumericalOrder_index | 1 | NumericalOrder | A | 208334 | NULL |
NULL | | BTREE | | | REE | | | |
wm_warehousestockpigextend | 1 | wm_warehousestockpigextend_NumericalOrder_Guid_index | 2 | Guid | A | 989249 | NULL | NULL | | BTREE | | | | wm_warehousestockpigextend |
1 | idx_Guid | 1 | Guid | A | 1002042 | NULL | NULL |
-----------------------+----------------+------------- ------------------------------------------+------ -------+----------------------+-----------+------- ------+----------+--------+------+------------+--- ------+---------------+
6 rows in set (0.00 sec)

3012 instance index:
mysql> show index from ms_transferhoursedetail m;
+------ ------------------+----------------+----------------- --------------------------+--------------+---------- ------------+-----------+-------------+----------+ --------+------+------------+---------+----------- ----+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------- --------+----------------+-------------------------- -------------+-------------+-------------------------- -+----------+-------------+----------+--------+-- ----+----------------+----------+---------------+
| ms_transferhoursedetail | 0 | PRIMARY | 1 | RecordID | A | 23432110 | NULL | NULL | | BTREE | | |
| ms_transferhoursedetail | 1 | idx_NumericalOrder | 1 | NumericalOrder | A | 2742415 | NULL |
| ms_transferhoursedetail | 1 | idx_NumericalOrder_PigID | 1 | NumericalOrder | A | 2054764 | NULL | NULL | | BTREE | | | ms_transferhoursedetail | 1 | idx_NumericalOrder_PigID | 2 | PigID | | |
|
ms_transferhoursedetail | 1 | idx_NumericalOrder_BatchID_PigID | 1 | NumericalOrder | A | 2594438 | NULL |
NULL | | BTREE | | | ms_transferhoursedetail
| 1 | idx_NumericalOrder_BatchID_PigID | 3 | PigID | A | 14737619 | NULL |
NULL | | BTREE | | | | ms_transferhoursedetail | 1 | idx_NumericalOrder_PigHouseUnitID_PigID | | ms_transferhoursedetail | 1
| idx_NumericalOrder_PigHouseUnitID_PigID | 2 | PigHouseUnitID | A | 4174213 | NULL |
NULL | | BTREE | | | | ms_transferhoursedetail |
1 | idx_NumericalOrder_PigHouseUnitID_PigID | 3 | PigID | _PigID_Abstract | 1 | PigID | A | 4534567 | NULL | NULL | | BTREE | | |
| ms_transferhoursedetail | 1 | idx_PigID_Abstract | 2 | Abstract | A | **88375 | NULL
| 1 | BatchID | A | 155804 | NULL | NULL | | BTREE | | | |
ms_transferhoursedetail | 1 | idx_BatchID_IsIn | 2 | IsIn | A | 289227 | NULL |
1 | NumericalOrderDetail | A | 15703572 | NULL | NULL | | BTREE | | |
| ms_transferhoursedetail | 1 | idx_NumericalOrderDetail_IsIn | 2 | IsIn | A | 23844024 | NULL | NULL | YES | BTREE | | | NULL
| | BTREE | | |
| ms_transferhoursedetail | 1 | idx_NumericalOrder_IsIn_Abstract | 2 | IsIn | A | 2513563 | NULL | NULL | YES | BTREE | | YES | BTREE | |
|
+ ------------------------+------------+---------- -------------------------------+--------------+---- ------------------+----------+-------------+----- -----+--------+------+------------+---------+----- ----------+
19 rows in set (0.00 sec)

mysql> show index from ms_transferhourse m0;
+-------------------+-- ----------+--------------------------------------- --+--------------+----------------+-----------+--- ----------+----------+--------+------+------------ +---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+------------ --------------------------+--------------+----- -----------+-----------+-------------+----------+- -------+------+------------+---------+------------ ---+
| ms_transferhourse | 0 | PRIMARY | 1 | RecordID | A | 2751201 | NULL | NULL | | BTREE | | | | ms_transferhourse |
1 | idx_NumericalOrder | 1 | NumericalOrder | | |
| ms_transferhourse | 1 | idx_EnterpriseID | 1 | EnterpriseID | A | 990 | NULL | NULL | | BTREE | | | | ms_transferhourse | 1 | idx_PigType |
1 | PigType | A | 2 | NULL | NULL | | BTREE | |
| ms_transferhourse | 1 | idx_DataDate | 1 | DataDate | A | 1639 | NULL | NULL | | BTREE | | |
| ms_transferhourse | 1 | idx_NumericalOrder_Abstract_IsIn | 1 | NumericalOrder | A | 2033616 | NULL | NULL | | BTREE | | | |
|
ms_transferhourse | 1 | idx_NumericalOrder_Abstract_IsIn | 3 | IsIn | A | 2769639 | NULL |
NULL | | BTREE | | | | ms_transferhourse | 1 | idx_PigFarmID_DataDate_PigType
| 1 | idx_PigFarmID_DataDate_PigType | 2 | DataDate | A | 432270 | NULL | NULL | |
BTREE | | | | ms_transferhourse | 1 | idx_PigFarmID_DataDate_PigType | 3 | PigType | A | 642796 | NULL
| x_PigFarmID_PigType_Abstract_DataDate | 1 | PigFarmID | A | 3009 | NULL | NULL | | BTREE | | | | ms_transferhourse | 1 | idx_PigFarmID_PigType_Abstract_DataDate | 2 | PigType | A |
7064 | NULL | Date | 3 | Abstract | A |
34188 | NULL | NULL | | BTREE | | |
| ms_transferhourse | 1 | idx_PigFarmID_PigType_Abstract_DataDate | 4 | DataDate |
----------+----------------+-------------------------- ---------------+--------------+----------------+-- ---------+-------------+----------+--------+------ +----------------+---------+---------------+
15 rows in set (0.00 sec)

mysql> show index from wm_warehousestockpigextend w;
+----------------------------+------------+- -------------------------------------------------- ----+--------------+----------------------+------- ----+-------------+----------+--------+------+---- --------+---------+--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------------+----------------+------- ------------------------------------------------+- -------------+----------------------+----------+- ------------+----------+--------+------+---------- --+---------+---------------+
| wm_warehousestockpigextend | 0 | PRIMARY | 1 | RecordID | A | 1027799 | NULL | NULL | | BTREE | | |
| wm_warehousestockpigextend | 1 | wm_warehousestockpigextend_NumericalOrderDetail_index | 1 | NumericalOrderDetail | A | **4447 The asterisk is **, I don’t know why itpub will code | NULL | NULL | | BTREE | | | | wm_warehousestockpigextend
| 1 | wm_warehousestockpigextend_NumericalOrder_index | 1 | NumericalOrder | A | 186737 | NULL |
NULL | | BTREE | | | | | |
| wm_warehousestockpigextend | 1 | wm_warehousestockpigextend_NumericalOrder_Guid_index | 2 | Guid | A | 1027832 | NULL | NULL | | BTREE | | | | wm_warehousestockpigextend | 1 | idx_Guid | 1 | Guid | A |
1008287 | NULL |
-----------------------+----------------+-------------- --------------------------------------------------+-------- ------+----------------------+-----------+-------- -----+----------+--------+------+------------+---- -----+---------------+
6 rows in set (0.00 sec)

Suggested fix:
you also can see:
http://www.itpub.net/forum.php?mod=viewthread&tid=2150410&page=1&extra=#pid23735133
[3 Nov 2023 5:18] harry harry 
Can delete this page?
[3 Nov 2023 13:05] MySQL Verification Team 
Hi Chenzhixin,

What do you want to remove? This bug report?

thanks
[3 Nov 2023 17:27] MySQL Verification Team 
Hi,

With regards to you report, if I understand correctly you have 2 slaves that have IDENTICAL data and IDENTICAL configuration (can you confirm this) and you execute SAME SELECT on them and you get different execution plans? Is this correct?
[4 Nov 2023 8:08] harry harry 
Yes, you understand very correctly. These two slave libraries were deployed on the same machine, using the same set of MySQL software to launch two instances. The data rows of both MySQL instances were the same, and statistical information was manually re collected through the analyze and optimize tables. The attachment is my trace file for executing SQL in two instances
[4 Nov 2023 12:54] MySQL Verification Team 
Hi,

I have no way of reproducing this. 

How is master executing this query?

Can you rebuild all tables in question on both slave's and then rerun the query? It is possible something is wrong there. 

Another thing, please do show create table for all tables in question separately on each slave and compare them, they should be identical but maybe someone messed with it at some time and didn't inform rest of the team.

And finally, please check status and config variables and compare them between the two, easiest way is to dump values into files and them use some visual diff to check what differs (I like araxis merge but there are lot of apps like that available)

With data we have attm we see that you have a problem but we have no way of saying why or how to solve it

Thanks
[5 Nov 2023 12:42] harry harry 
Thank you.