Mysql – Trying to optimize query for performance (MySQL)

MySQLoptimizationperformance

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

  • Get the 100 keys from ProjectManagement2 upfront
  • join everything afterwards

Here is my proposed query

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
(
    SELECT B.* FROM
    (SELECT id FROM ProjectManagement2 LIMIT 50000, 100) A
    INNER JOIN ProjectManagement2 B USING (id)
) 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
;

Give it a Try !!!