Mark89 Mark89 - 2 months ago 24
MySQL Question

query optimization, sql

Do you have any idea how to optimize this query:

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`),
)

Answer

You Can use the following query

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

Then Create an Index

Create Clustered Index Temp On  #Temp(wid)

Now Use Join

Select b.* 
from branch b 
join #Temp t on t.wid=b.wid where st = 1