Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Is there an index on Issues.issueSecurityLevelId ? –  ypercube Apr 6 '12 at 12:38
    
yea, all the fields that I am joining on have indexes. –  ryanzec Apr 6 '12 at 12:54
    
How does it perform when looking for records near the start of the set. –  BillThor Apr 6 '12 at 12:56
    
Better but still around 180-200ms without the order by but if I add the order by, and order by a field on the Issues table, it still takes several seconds (and more times then not, I will have an order by). –  ryanzec Apr 6 '12 at 13:08
    
Is this SQL Server? –  Leigh Riffel Apr 6 '12 at 13:49
show 1 more comment

2 Answers 2

up vote 5 down vote accepted

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

share|improve this answer
    
This is a good proposal for better performance. But isn't there a chance that it will return different result set? Some of the joining columns may allow Nulls. –  ypercube Apr 6 '12 at 20:03
    
@ypercube The subquery i is designed to get the 100 primary key ids and then get the 100 whole rows (B.*) from the ProjectManagement2 table. The joins should work the same as the original query. If @ryanzec had LEFT JOINs in his original query, my proposed query would have it as well. The idea is just to reverse the order of retrieval from the MySQL Query Optimizer's viewpoint. @ryanzec would have to deal with the LEFT JOIN/INNER JOIN issues and decide thereafter. –  RolandoMySQLDBA Apr 6 '12 at 20:12
    
Yeah, it will most probably work OK. If all FK columns have been defined as NOT NULL and with Cascade effects (for both Updates and Deletes). –  ypercube Apr 6 '12 at 20:19
    
In all honesty, I should be doing left joins on the ones that allow NULL anyways. –  ryanzec Apr 10 '12 at 9:07
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.