Why mysql query is taking too much time to fetch results?

user_13885407 0
Viewed 68

I have a database that contains two tables one is users and users_activity.

Users Activity table have some columns including userid that match's with user table's id.

I want to get some result from both tables using JOINS.

Here's my MySQL query. Please let me know what's my mistake / Why this query is taking too much time?

SELECT ua.userid, COUNT(ua.id) user_active, u.name 
FROM `user_activity` ua, users u 
WHERE   ua.target = 'Save Post' OR 
        ua.target = 'Send comment' OR 
        ua.target = 'Send Reply' 
        AND ua.created_on >= 1630454401 
        AND ua.userid = u.id 
GROUP BY ua.userid 
ORDER BY COUNT(ua.userid) DESC LIMIT 5

Thank you for your help.

mysql php
2 Answers

Put brackets around bits of your where clause if you are using AND and OR together otherwise the DB will do a lot more than you actually intended it to do, depending on what order you place things

SELECT ua.userid, COUNT(ua.id) user_active, u.name 
    FROM `user_activity` ua, users u 
    WHERE 
        (
        ua.target = 'Save Post' OR 
        ua.target = 'Send comment' OR 
        ua.target = 'Send Reply' 
        )
    AND ua.created_on >= 1630454401 
    GROUP BY ua.userid 
    ORDER BY COUNT(ua.userid) DESC 
    LIMIT 5

Of course if you are processing a billion rows some time will be involved

Also you should get used to the JOIN syntax rather than the old comma seperated list of tables. They are much more flexible and efficient

SELECT ua.userid, COUNT(ua.id) user_active, u.name 
FROM user_activity ua 
    JOIN users u ON ua.userid = u.id
WHERE 
    (
    ua.target = 'Save Post' OR 
    ua.target = 'Send comment' OR 
    ua.target = 'Send Reply' 
    )
AND ua.created_on >= 1630454401 
AND ua.userid = u.id 
GROUP BY ua.userid 
ORDER BY COUNT(ua.userid) DESC 
LIMIT 5
user_23108300 
  SELECT ua.userid, COUNT(ua.id)  As user_active, u.name 
    FROM `user_activity` ua INNER JOIN users u 
     ON ua.userid = u.id  
    AND  ua.target IN ( 'Save Post' ,'Send comment','Send Reply') 
            AND ua.created_on >= 1630454401 
           GROUP BY ua.userid  ORDER BY COUNT(ua.userid) DESC LIMIT 5
user_151595410
Related