根据题主的问题,你那条SQL条件那么多,但是只能用到一个索引,岂不可惜,WHERE条件很明显的一处:如下的那个'OR':
(
(
(
(`from_uid` = 5017446 AND `from_type` = 1 AND `to_uid` = 52494 AND `to_type` = 3)
OR
(`from_uid` = 52494 AND `from_type` = 3 AND `to_uid` = 5017446 AND `to_type` = 1)
)
AND `type` = 2 AND `qa_id` = 0
)
OR ------------------- 此处这个OR ----------------------------------
(`type` = 3 AND `to_uid` = 52494 AND `to_type` = 3 AND `from_uid` = 5017446 AND `from_type` = 1
AND `module` IN ('community.doctor:appointment:notice' ,
'community.doctor:transfer.treatment',
'community.doctor:transfer.treatment.pay',
'community.doctor:weiyi.guahao.to.user',
'community.doctor:weiyi.prescription.to.patient',
'community.doctor:user.buy.prescription')
)
)
AND `status` = 1
AND `record_global_id` < 5407938
可以将整体的大的WHERE分拆开来,思路就是 UNION,好了,直接贴我改造后的结果SQL,如果有作用望采纳呦^_^
改造后SQL:
(
SELECT
`record_global_id`,
`type`,
`mark`,
`from_uid`,
`from_type`,
`to_uid`,
`to_type`,
`send_method`,
`action`,
`module`,
`send_time`,
`content`
FROM
`im_data_record`
WHERE
(
(`from_uid` = 5017446 AND `from_type` = 1 AND `to_uid` = 52494 AND `to_type` = 3)
OR
(`from_uid` = 52494 AND `from_type` = 3 AND `to_uid` = 5017446 AND `to_type` = 1)
)
AND `type` = 2 AND `qa_id` = 0 AND `status` = 1
AND `record_global_id` < 5407938
)
UNION
(
SELECT
`record_global_id`,
`type`,
`mark`,
`from_uid`,
`from_type`,
`to_uid`,
`to_type`,
`send_method`,
`action`,
`module`,
`send_time`,
`content`
FROM
`im_data_record`
WHERE
`type` = 3 AND `to_uid` = 52494 AND `to_type` = 3 AND `from_uid` = 5017446 AND `from_type` = 1
AND `module` IN ('community.doctor:appointment:notice' ,
'community.doctor:transfer.treatment',
'community.doctor:transfer.treatment.pay',
'community.doctor:weiyi.guahao.to.user',
'community.doctor:weiyi.prescription.to.patient',
'community.doctor:user.buy.prescription')
AND `status` = 1 AND `record_global_id` < 5407938
)
ORDER BY `record_global_id` DESC
LIMIT 0 , 20;
如有作用能将执行计划截图发到评论里吗?我想验证下我的猜想,谢谢!