MySQL: Query Optimization

SELECT a.FundIDRecv,a.SubscribeDt, b.FundName, 
(
    SELECT SUM(c.PricePerWeek) 
    FROM tbl_Hive c
    WHERE c.FundID IN 
    (
        SELECT FundID from tbl_FundStatic 
        WHERE FundID IN
        (
            SELECT FundIDSend
            FROM tbl_FundSubscriptions 
            WHERE FundIDRecv = a.FundIDRecv
            AND SubscribeDt >= subdate(CURDATE(), INTERVAL weekday(CURDATE()) DAY)
        )
        AND U开发者_如何学CserID = '14'
    )
) as Price
FROM tbl_FundSubscriptions a, tbl_Hive b
WHERE a.FundIDRecv = b.FundID
AND a.SubscribeDt >= subdate(CURDATE(), INTERVAL weekday(CURDATE()) DAY)
AND a.FundIDRecv IN
(
    SELECT FundIDRecv
    FROM tbl_FundSubscriptions
    WHERE FundIDSend IN (
        SELECT FundID
        FROM tbl_FundStatic
        WHERE UserID = '14'
    )
)
GROUP BY FundIDRecv

This Query takes a huge amount of time to fetch data.

How can I optimize this query so as to make it execute and fetch results faster than it does?

---

Replace your nested selects by JOIN clauses. Take this for instance:

AND a.FundIDRecv IN
(
    SELECT FundIDRecv
    FROM tbl_FundSubscriptions
    WHERE FundIDSend IN (
        SELECT FundID
        FROM tbl_FundStatic
        WHERE UserID = '14'
    )
)

Why not just join tbl_FundStatic to the outer query like this

FROM tbl_FundSubscriptions a
JOIN tbl_FundStatic s ON (a.FundIDSend = s.FundID)
WHERE s.UserID = '14'

Of course, I don't know if this is still correct, but it'll give you an idea. Also, you should avoid nested selects in the SELECT clause itself. It's better to join tbl_FundStatic and then select fields from it

---

Untested but this will give you a go:

SELECT a.fundidrecv, 
       a.subscribedt, 
       b.fundname, 
       SUM(b.priceperweek) AS price 
FROM   tbl_fundsubscriptions a 
       JOIN tbl_hive b 
         ON a.fundidrecv = b.fundid 
       JOIN tbl_fundsubscriptions fs 
         ON fs.fundidrecv = a.fundidrecv 
       JOIN tbl_fundstatic fst 
         ON fst.fundid = fs.fundidsend 
            AND fst.userid = '14' 
WHERE  a.subscribedt >= SUBDATE(Curdate(), INTERVAL Weekday(Curdate()) DAY) 
GROUP  BY a.fundidrecv 

You need to add indexes on the following columns:

• (a.fundidrecv,a.subscribedt)
• (b.fundid)
• (fs.fundidrecv)
• (fst.fundid,fst.userid)