mysql in 查询效率问题
问题如下, 现有2个sql
第一个如下: 查询返回 50 条, 查询时间 150ms
select
t1.id,
t1.order_no,
t1.status,
t1.company_id,
t1.company_name,
t1.dept_id,
t1.dept_name,
t1.room_id,
t1.room_name,
t1.doctor_id,
t1.doctor_name,
t1.medical_id,
t1.medical_name,
t1.medical_number,
t1.is_transfer,
t1.doctor_id_before,
t1.doctor_name_before,
t1.sign_time,
t2.ref_type
from
dts_treatment_order t1,
dts_treatment_doctor_ref t2
where
t1.id = t2.ref_order_id
and t2.ref_doctor_id = 1800337755708911618
and t1.plan_day = 1718121600000
and t1.company_id = 1787320614174867457
and t1.status in ('SIGNED')
and t1.is_delete = 'N'
order by
gmt_modify desc
第二个如下:
在 in 条件中随意加一个字符 , 查询速度 为 15ms 左右, 提升了10倍
不知是何原因造成的,
select
t1.id,
t1.order_no,
t1.status,
t1.company_id,
t1.company_name,
t1.dept_id,
t1.dept_name,
t1.room_id,
t1.room_name,
t1.doctor_id,
t1.doctor_name,
t1.medical_id,
t1.medical_name,
t1.medical_number,
t1.is_transfer,
t1.doctor_id_before,
t1.doctor_name_before,
t1.sign_time,
t2.ref_type
from
dts_treatment_order t1,
dts_treatment_doctor_ref t2
where
t1.id = t2.ref_order_id
and t2.ref_doctor_id = 1800337755708911618
and t1.plan_day = 1718121600000
and t1.company_id = 1787320614174867457
and t1.status in ('SIGNED', 'xxxx')
and t1.is_delete = 'N'
order by
gmt_modify desc
;