So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense. The basic query looks like:
SELECT i.id, i.`key`, i.title, i.description,
CONCAT(ru.firstName, ' ', ru.lastName) as `name`,
CONCAT(au.firstName, ' ', au.lastName) as `name`,
p.title, pc.title, pva.title, pvo.title, pvf.title, i.durationEstimate,
i.storyPoints, i.dueDate, isl.title, i.rejectionCount,
CONCAT(uc.firstName, ' ', uc.lastName) as `name`,
i.createdTimestamp, i.updatedTimestamp, it.title, isss.title
FROM ProjectManagement2.Issues i
INNER JOIN Users ru ON ru.id = i.reporterUserUsername
INNER JOIN Users au ON au.id = i.assignedUserUsername
INNER JOIN Projects p ON p.id = i.projectTitle
INNER JOIN ProjectComponents pc ON pc.id = i.projectComponentTitle
INNER JOIN ProjectVersions pva ON pva.id = i.affectedProjectVersionTitle
INNER JOIN ProjectVersions pvo ON pvo.id = i.originalFixedProjectVersionTitle
INNER JOIN ProjectVersions pvf ON pvf.id = i.fixedProjectVersionTitle
INNER JOIN IssueSecurityLevels isl ON isl.id = i.issueSecurityLevelId
INNER JOIN IssueTypes it ON it.id = i.issueTypeId
INNER JOIN IssueStatuses isss ON isss.id = i.issueStatusId
INNER JOIN Users uc ON uc.id = i.creatorUserUsername
LIMIT 50000, 100;
This query takes about 880ms to run after I run it the first time (which take a few seconds). The EXPLAIN of this query shows:
1 SIMPLE pvo index PRIMARY title 98 NULL 22 Using index
1 SIMPLE i ref reporterUserUsername,assignedUserUsername,projectTitle,projectComponentTitle,affectProjectVersionTitle,originalFixedProjectVersionTitle,fixedProjectVersionTitle,issueSecurityLevelId,creatorUserUsername,issueTypeId,issueStatusId
originalFixedProjectVersionTitle 5 ProjectManagement2.pvo.id 1136 Using where
1 SIMPLE isl ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer
1 SIMPLE isss eq_ref PRIMARY PRIMARY 4 ProjectManagement2.i.issueStatusId 1
1 SIMPLE pc eq_ref PRIMARY PRIMARY 4 ProjectManagement2.i.projectComponentTitle 1
1 SIMPLE pva eq_ref PRIMARY PRIMARY 4 ProjectManagement2.i.affectedProjectVersionTitle 1
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 ProjectManagement2.i.projectTitle 1
1 SIMPLE pvf eq_ref PRIMARY PRIMARY 4 ProjectManagement2.i.fixedProjectVersionTitle 1
1 SIMPLE ru eq_ref PRIMARY PRIMARY 4 ProjectManagement2.i.reporterUserUsername 1
1 SIMPLE au eq_ref PRIMARY PRIMARY 4 ProjectManagement2.i.assignedUserUsername 1
1 SIMPLE uc eq_ref PRIMARY PRIMARY 4 ProjectManagement2.i.creatorUserUsername 1
1 SIMPLE it eq_ref PRIMARY PRIMARY 4 ProjectManagement2.i.issueTypeId
The big issue is when I add a order by to the query, then it take several seconds after the first query.
Is there anything I can do to optimize this query (if you need any more information please let me know)?
Best Answer
Your original query joins everything together then traverses 50000 rows into the joined data before presenting the next 100 rows. Try this
Here is my proposed query
Give it a Try !!!