MySQL: Query Optimization

Question

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 UserID = '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?

Depends. What are your tables and indexes? That makes a big difference. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Mar 16, 2011 at 11:04
Please give DB structures (tables/indexes) and execution plan (explain) for your query — oryol
– oryol, Commented Mar 16, 2011 at 11:25

Lukas Eder · Accepted Answer · 2011-03-16 11:03:27Z

3

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

answered Mar 16, 2011 at 11:03

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Parth Bhatt Over a year ago

How can I do that? Can you provide more information on how to do that? I have no idea about it

Parth Bhatt Over a year ago

Yeah i will try out that. Thanks for the input though.

Pentium10 · Accepted Answer · 2011-03-16 11:23:59Z

3

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)

answered Mar 16, 2011 at 11:23

Pentium10

209k124 gold badges435 silver badges513 bronze badges

3 Comments

Parth Bhatt Over a year ago

I will check it soon and get back to you.

Parth Bhatt Over a year ago

@Pentuim10 : Thanks for the input. I appreciate your effort but SUM(b.priceperweek) is showing wrong data. It does not show the expected result.

Pentium10 Over a year ago

Update your original question, and describe what the query should do.

Collectives™ on Stack Overflow

MySQL: Query Optimization

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related