Description:
my Colleague gave me a SQL.Evidently it is a great sql when we checked it's Execution Plan Information.
but when we added a json function(JSON_VALID()) in the end
as a condition ; Execution Plan Information was unexpected .
it's a very simple SQL.I checked it in anther mysql server.nothing changes.
Hope to get a reply
How to repeat:
explain
select count(1)
from test a
where 1=1
and a.create_date_ >= DATE_FORMAT(concat(DATE_FORMAT(date_add(now(),interval 0 month),'%Y%m'),'01'),'%Y%m%d')
and a.create_date_ < DATE_FORMAT(concat(DATE_FORMAT(date_add(now(),interval 1 month),'%Y%m'),'01'),'%Y%m%d')
and a.ext_task_type_ = 'FTSP_KH_BBSJ_BATCH'
and a.ext_swjg_code_ IN ('11100000000' , '14400000000');
Execution Plan Information :
+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------+------------------------------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------+------------------------------------------------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | a | NULL | range | i_bap_create_date_end_task_id_,i_bap_create_date_ext_task_type_ext_swjg_code_ | i_bap_create_date_ext_task_type_ext_swjg_code_ | 285 | NULL | 427700 | 2.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-------------------------------------------------------------------------------+------------------------------------------------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
but when we added a json function(and JSON_VALID(a.ext_task_params_) =1) in the end
as a condition ; Execution Plan Information was unexpected .
+----+-------------+-------+------------+------+-------------------------------------------------------------------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------------------------------------------------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | i_bap_create_date_end_task_id_,i_bap_create_date_ext_task_type_ext_swjg_code_ | NULL | NULL | NULL | 1024053 | 0.58 | Using where |
+----+-------------+-------+------------+------+-------------------------------------------------------------------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Description: my Colleague gave me a SQL.Evidently it is a great sql when we checked it's Execution Plan Information. but when we added a json function(JSON_VALID()) in the end as a condition ; Execution Plan Information was unexpected . it's a very simple SQL.I checked it in anther mysql server.nothing changes. Hope to get a reply How to repeat: explain select count(1) from test a where 1=1 and a.create_date_ >= DATE_FORMAT(concat(DATE_FORMAT(date_add(now(),interval 0 month),'%Y%m'),'01'),'%Y%m%d') and a.create_date_ < DATE_FORMAT(concat(DATE_FORMAT(date_add(now(),interval 1 month),'%Y%m'),'01'),'%Y%m%d') and a.ext_task_type_ = 'FTSP_KH_BBSJ_BATCH' and a.ext_swjg_code_ IN ('11100000000' , '14400000000'); Execution Plan Information : +----+-------------+-------+------------+-------+-------------------------------------------------------------------------------+------------------------------------------------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-------------------------------------------------------------------------------+------------------------------------------------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | a | NULL | range | i_bap_create_date_end_task_id_,i_bap_create_date_ext_task_type_ext_swjg_code_ | i_bap_create_date_ext_task_type_ext_swjg_code_ | 285 | NULL | 427700 | 2.00 | Using where; Using index | +----+-------------+-------+------------+-------+-------------------------------------------------------------------------------+------------------------------------------------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) but when we added a json function(and JSON_VALID(a.ext_task_params_) =1) in the end as a condition ; Execution Plan Information was unexpected . +----+-------------+-------+------------+------+-------------------------------------------------------------------------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------------------------------------------------------------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | a | NULL | ALL | i_bap_create_date_end_task_id_,i_bap_create_date_ext_task_type_ext_swjg_code_ | NULL | NULL | NULL | 1024053 | 0.58 | Using where | +----+-------------+-------+------------+------+-------------------------------------------------------------------------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)