Thank you for your help! If the status of the bug report you submitted changes,
you will be notified. You may return here and check on the status or update
your report at any time. That URL for your bug report is:
http://bugs.mysql.com/113035.
Description:
My MySQL version is MySQL 8.0.32.
There are three tables inner join, but mysql optimizer selected wrong driver table.
How to repeat:
For the following SQL statement, the optimizer of MySQL actually selected the wrong execution plan and used the w table as the driver table:
mysql> desc SELECT
-> m0.NumericalOrder
-> FROM
-> ms_transferhoursedetail m
-> INNER JOIN ms_transferhourse m0 ON m.NumericalOrder = m0.NumericalOrder
-> AND m.IsIn = m0.IsIn
-> AND m.Abstract = m0.Abstract
-> INNER JOIN 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' );
+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+---------------------------------------------------------------------------+---------+----------+------------------------------------+
| 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 | 1068620 | 100.00 | Using index |
| 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_NumericalOrderDetail_IsIn | 8 | w.NumericalOrderDetail | 1 | 100.00 | Using index condition; Using where |
| 1 | SIMPLE | m0 | NULL | ref | idx_NumericalOrder,idx_DataDate,idx_NumericalOrder_Abstract_IsIn | idx_NumericalOrder_Abstract_IsIn | 17 | m.NumericalOrder,m.Abstract,m.IsIn | 1 | 8.45 | Using where |
+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+---------------------------------------------------------------------------+---------+----------+------------------------------------+
3 rows in set, 1 warning (0.00 sec)
This SQL statement takes 1 minute to run.
I have execute analyze table,optimize table,alter table xx engine=innodb, it always use w table as drive table.
So I used hint straight_ Join to force the m0 table as the driver table:
mysql> desc SELECT
-> m0.NumericalOrder
-> FROM
-> ms_transferhoursedetail m
-> INNER JOIN ms_transferhourse m0 ON m.NumericalOrder = m0.NumericalOrder
-> AND m.IsIn = m0.IsIn
-> AND m.Abstract = m0.Abstract
-> STRAIGHT_JOIN 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' );
+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------------+--------+----------+------------------------------------+
| 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 | 239096 | 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_NumericalOrder_IsIn_Abstract | 19 | m0.NumericalOrder,m0.IsIn,m0.Abstract | 7 | 100.00 | NULL |
| 1 | SIMPLE | w | NULL | ref | wm_warehousestockpigextend_NumericalOrderDetail_index | wm_warehousestockpigextend_NumericalOrderDetail_index | 8 | m.NumericalOrderDetail | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------------+--------+----------+------------------------------------+
3 rows in set, 1 warning (0.01 sec)
After using this hint, this statement only takes 400ms to run.
Let's analyze why m0 table is suitable as a driver table.
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)
mysql>select count(*) from ms_transferhourse m0
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' );
+----------+
| count(*) |
+----------+
| 33 |
+----------+
1 row in set (0.28 sec)
mysql> desc select count(*) from ms_transferhourse m0
-> 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' );
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------------+
| 1 | SIMPLE | m0 | NULL | range | idx_DataDate | idx_DataDate | 3 | NULL | 239096 | 100.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
We know that small tables are used as driver tables.
From the number of rows above, it can be seen that m0 is indeed a suitable driver table, as after filtering through the where condition, only 33 rows of data are available, m0 table is small, not w table.
May I ask why MySQL 8.0.32 chooses wrong drive tables in this situation.
Description: My MySQL version is MySQL 8.0.32. There are three tables inner join, but mysql optimizer selected wrong driver table. How to repeat: For the following SQL statement, the optimizer of MySQL actually selected the wrong execution plan and used the w table as the driver table: mysql> desc SELECT -> m0.NumericalOrder -> FROM -> ms_transferhoursedetail m -> INNER JOIN ms_transferhourse m0 ON m.NumericalOrder = m0.NumericalOrder -> AND m.IsIn = m0.IsIn -> AND m.Abstract = m0.Abstract -> INNER JOIN 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' ); +----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+---------------------------------------------------------------------------+---------+----------+------------------------------------+ | 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 | 1068620 | 100.00 | Using index | | 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_NumericalOrderDetail_IsIn | 8 | w.NumericalOrderDetail | 1 | 100.00 | Using index condition; Using where | | 1 | SIMPLE | m0 | NULL | ref | idx_NumericalOrder,idx_DataDate,idx_NumericalOrder_Abstract_IsIn | idx_NumericalOrder_Abstract_IsIn | 17 | m.NumericalOrder,m.Abstract,m.IsIn | 1 | 8.45 | Using where | +----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+---------------------------------------------------------------------------+---------+----------+------------------------------------+ 3 rows in set, 1 warning (0.00 sec) This SQL statement takes 1 minute to run. I have execute analyze table,optimize table,alter table xx engine=innodb, it always use w table as drive table. So I used hint straight_ Join to force the m0 table as the driver table: mysql> desc SELECT -> m0.NumericalOrder -> FROM -> ms_transferhoursedetail m -> INNER JOIN ms_transferhourse m0 ON m.NumericalOrder = m0.NumericalOrder -> AND m.IsIn = m0.IsIn -> AND m.Abstract = m0.Abstract -> STRAIGHT_JOIN 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' ); +----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------------+--------+----------+------------------------------------+ | 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 | 239096 | 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_NumericalOrder_IsIn_Abstract | 19 | m0.NumericalOrder,m0.IsIn,m0.Abstract | 7 | 100.00 | NULL | | 1 | SIMPLE | w | NULL | ref | wm_warehousestockpigextend_NumericalOrderDetail_index | wm_warehousestockpigextend_NumericalOrderDetail_index | 8 | m.NumericalOrderDetail | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------------+--------+----------+------------------------------------+ 3 rows in set, 1 warning (0.01 sec) After using this hint, this statement only takes 400ms to run. Let's analyze why m0 table is suitable as a driver table. 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) mysql>select count(*) from ms_transferhourse m0 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' ); +----------+ | count(*) | +----------+ | 33 | +----------+ 1 row in set (0.28 sec) mysql> desc select count(*) from ms_transferhourse m0 -> 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' ); +----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------------+ | 1 | SIMPLE | m0 | NULL | range | idx_DataDate | idx_DataDate | 3 | NULL | 239096 | 100.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------------+ 1 row in set, 1 warning (0.00 sec) We know that small tables are used as driver tables. From the number of rows above, it can be seen that m0 is indeed a suitable driver table, as after filtering through the where condition, only 33 rows of data are available, m0 table is small, not w table. May I ask why MySQL 8.0.32 chooses wrong drive tables in this situation.