CODEWITHSUNDEEP
mysqlsqlsubquery
Kristoffer
Kristoffer

Reputation: 2024

Mysql subquery very slow

I have this MySQL question. It works fine, but it's extremly slow. It takes up to 30 seconds to run.

SELECT t.Id, Subject, k.info
FROM tip t
LEFT JOIN comments k ON t.Id = k.Tipid
WHERE event = 1
AND k.Id
IN (
SELECT Max(Id) FROM comments GROUP BY Tipid
)

If I run only the subquery (SELECT Max(Id) FROM comments GROUP BY Tipid) it takes about 0.02 seconds to run.

Something is wrong with the subquery. Do you have any ideas?

Upvotes: 2

Views: 205

Answers (3)

Nilesh Gupta
Nilesh Gupta

Reputation: 367

There are many reason for this... i think u try this one...

SELECT t.Id, Subject, k.info
FROM tip t
INNER JOIN comments k ON t.Id = k.Tipid
WHERE event = 1
  AND k.Id IN ( SELECT Max(Id) FROM comments GROUP BY Tipid )

Because LEFT JOIN is become too slow after 50 records.

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

you dont need group by if you select max(id) try this

    SELECT t.Id, Subject, k.info
    FROM tip t
    LEFT JOIN comments k ON t.Id = k.Tipid
    WHERE event = 1
    AND k.Id in (select Max(Id) from comments)

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this instead:

SELECT t.Id, t.Subject, c.info
FROM tip t
LEFT JOIN
(
   SELECT Tipid, MAX(Id) AS MaxId
   FROM comments 
   GROUP BY Tipid
) AS c  ON t.Id = c.Tipid
       AND t.Id = c.MaxId
WHERE t.event = 1;

Upvotes: 1

Related Questions