Trying to optimize query for performance (MySQL)

Question

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

Answer 1

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 !!!

Answer 2

Scanning 50000 rows into the result set will take a while. Looking 50 rows into the result set may be more realistic. I have worked on several projects which limit the result set size for interactive queries to somewhere between 200 and and 500 rows.

A few things that may help:

• For optimizers that use rule based approaches it may help to have the driving table (Issues) listed last. Parsers often work from the end of the query up.
• Having selection criteria which significantly reduce the percentage of rows retrieved helps.
• Selecting a row subset from an ordered result set may require the whole result set to be computed and ordered, or the driving table to randomly access data via the index.
• Some optimizers will allow you to set how many permutations are used. More is better if queries are frequently used and execution plans are cached.
• Use OUTER joins when the joined field is nullable. If the field really is not nullable, change it to NOT NULL. NOTE: Using an OUTER join when there are null values can change the results returned.
• Careful use of hints can result in faster queries. They can also severely degrade performance. Test without hints before adding them.

EDIT: In your particular case selecting 100 rows 50000 rows into the result set it may be appropriate to use an IN clause and locate the 100 ids you are interested using as few tables as possible. (As a general rule eliminate any tables joined on the full primary key.) This limits most of the joins to 10 cases. As always test both cases.