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.