您是否知道如何优化此查询：
SELECT
*
FROM
branch
WHERE
wid IN (
SELECT
MAX(wid) AS wid
FROM
branch
WHERE
og = 'XXX'
AND st = 'YYY'
AND guide IN ('...')
AND uid IN ('...')
GROUP BY
uid,
guide
)
AND stp = 1;
CREATE TABLE `branch` (
`wid` bigint(20) unsigned NOT NULL,
`branchid` int(11) NOT NULL,
`stp` int(11) NOT NULL,
`st` varchar(16) DEFAULT NULL,
`og` varchar(32) DEFAULT NULL,
`uid` bigint(20) unsigned NOT NULL,
...
PRIMARY KEY (`wid`,`branchid`,`stp`),
KEY `branch_guide_d` (`guide`,`d`),
KEY `branch_st_c` (`st`,`c`),
KEY `branch_st_m` (`st`,`m`),
KEY `branch_uid_guide_wid` (`uid`,`guide`,`wid`),
)
您可以使用以下查询
SELECT
MAX(wid) AS wid into #Temp
FROM
branch
WHERE
og = 'XXX'
AND st = 'YYY'
AND guide IN ('...')
AND uid IN ('...')
GROUP BY
uid,
guide
然后创建一个索引
Create Clustered Index Temp On #Temp(wid)
现在使用加入
Select b.*
from branch b
join #Temp t on t.wid=b.wid where st = 1
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句