Description:
mysql explain filtered inaccurate when find_in_set function used
How to repeat:
This sql return actual rows is 33:
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.51 sec)
But this sql explain estimate return rows is: 251750 * 100% = 251750, I think the result is wrong.
mysql> explain 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 | 251750 | 100.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
Now I drop the index idx_DataDate :
mysql> alter table ms_transferhourse drop index idx_DataDate ;
Query OK, 0 rows affected (7.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
And then run this sql explain again:
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' ) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m0
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2845859
filtered: 5.58
Extra: Using where
1 row in set, 1 warning (0.00 sec)
Now this sql explain return the estimate is :2845859 * 5.58% = 165, Near true value 33.
I think mysql exlain filterd is not inaccurate in the above situation.
Description: mysql explain filtered inaccurate when find_in_set function used How to repeat: This sql return actual rows is 33: 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.51 sec) But this sql explain estimate return rows is: 251750 * 100% = 251750, I think the result is wrong. mysql> explain 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 | 251750 | 100.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------------+ 1 row in set, 1 warning (0.00 sec) Now I drop the index idx_DataDate : mysql> alter table ms_transferhourse drop index idx_DataDate ; Query OK, 0 rows affected (7.29 sec) Records: 0 Duplicates: 0 Warnings: 0 And then run this sql explain again: 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' ) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: m0 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2845859 filtered: 5.58 Extra: Using where 1 row in set, 1 warning (0.00 sec) Now this sql explain return the estimate is :2845859 * 5.58% = 165, Near true value 33. I think mysql exlain filterd is not inaccurate in the above situation.