CODEWITHSUNDEEP
mysqlquery-optimization
matt
matt

Reputation: 2352

MySQL optimise query

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?

Upvotes: 0

Views: 63

Answers (2)

Jason Heo
Jason Heo

Reputation: 10236

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);

Upvotes: 2

Raul Andres
Raul Andres

Reputation: 3806

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

EXPLAIN

Upvotes: 0

Related Questions