MySQL optimise query

Question

SELECT 
    (UserCourseProgress.TimeEnded - UserCourseProgress.TimeStarted) as SecondsSpent,
    UserCourseProgress.UserId 
FROM 
    UserCourseProgress 
    INNER JOIN UserRoles 
    ON UserCourseProgress.UserId = UserRoles.UserId 
       AND (UserRoles.SuperId = '123' OR UserRoles.UserId = '123')

I'm trying to optimise this query as it takes too long (mainly Copying Data To Tmp Table). The structure is simple a table called UserCourseProgress and another one called UserRoles, they both have the UserId column in common and I'm selecting all userRoles that belong to a super user. Any ideas?

Make sure you have indexes on UserRoles.SuperId and UserRoles.UserId, as well as on the FK UserCourseProgress.UserId — StuartLC
– StuartLC, Commented Jan 10, 2014 at 12:53
can you give the table structure ,and little more explanation, — Lijo
– Lijo, Commented Jan 10, 2014 at 12:57

Jason Heo · Accepted Answer · 2014-01-10 13:24:32Z

2

Use UNION

SELECT
    (UserCourseProgress.TimeEnded - UserCourseProgress.TimeStarted) as SecondsSpent,
    UserCourseProgress.UserId FROM UserCourseProgress 
INNER JOIN UserRoles ON UserCourseProgress.UserId = UserRoles.UserId
    AND UserRoles.SuperId = '123'

UNION

SELECT
    (UserCourseProgress.TimeEnded - UserCourseProgress.TimeStarted) as SecondsSpent,
    UserCourseProgress.UserId FROM UserCourseProgress 
INNER JOIN UserRoles ON UserCourseProgress.UserId = UserRoles.UserId
    AND UserRoles.UserId = '123';

Add INDEX

ALTER TABLE UserCourseProgress ADD INDEX (UserId);
ALTER TABLE UserRoles ADD INDEX (SuperId, UserId);
ALTER TABLE UserRoles ADD INDEX (UserId);

edited Jan 10, 2014 at 13:24

answered Jan 10, 2014 at 12:59

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

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

Comments

Raul Andres · Accepted Answer · 2014-01-10 12:55:24Z

0

Use EXPLAIN to obtain the query execution plan. Then, look the more consuming steps and use index if necessary.

EXPLAIN

answered Jan 10, 2014 at 12:55

Raul Andres

3,81617 silver badges24 bronze badges

Collectives™ on Stack Overflow

MySQL optimise query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related