CODEWITHSUNDEEP
mysql
Juan
Juan

Reputation: 439

How to optimze mysql subquery?

Need help to optimize my query, currently it runs approximately 22secs and I have 1.1 million rows in that table. Here is my query. This query is returning the users latest time in.

SELECT 
    t.id, t.user_id, t.created
FROM
    timesheets t
WHERE
    t.user_id IN (41649 , 41901,41899,41482,41648,41897,41652,
        41483,41900,41143,41907,16,42191,42195,42194,42136,42193,42190,42484,42485,42486,42504,
        42052,42797,42997,42868,42847,42505,42964)
    AND t.id = (SELECT 
                    MAX(sub_t.id)
                FROM
                    timesheets AS sub_t
                WHERE
                    sub_t.user_id = t.user_id
                AND sub_t.description = 'in')

I also have table indexes for this table (id, user_id, created, description). Any suggestion is very much appreciated. Thanks

Upvotes: 0

Views: 35

Answers (1)

Barmar
Barmar

Reputation: 780673

A multi-column index (user_id, id) would probably help.

Also, maybe use a join instead of correlated subquery.

SELECT 
    t.id, t.user_id, t.created
FROM
    timesheets t
JOIN (
    SELECT MAX(id) AS maxid
    FROM timesheets t
    WHERE user_id IN (41649 , 41901,41899,41482,41648,41897,41652,
            41483,41900,41143,41907,16,42191,42195,42194,42136,42193,42190,42484,42485,42486,42504,
            42052,42797,42997,42868,42847,42505,42964)
        AND description = "in"
    GROUP BY user_id) t1 
ON t.id = t1.maxid

I'm assuming id is the primary key of the table, so it's a unique index.

Upvotes: 1

Related Questions