Re: How to simplify for speed

[David West]

Joshua,

I am not sure if this will work! I have made some radical changes to the structure of
the SQL however I am a bit dubious of the first Derived table (it should be able to be
included into the main From clause).

I am not sure if MySQL supports outer joining to a constant but it would be more
efficient than using a "NOT IN".

I took out a lot of references to tables not referenced in your derived tables.

If the BugTypeId, PriorityId and CustomerId fields are mandatory then you can remove the "is not null" lines from the where clauses. They are only there since I don't know the design of the table and joining to the tables will filter out the nulls.

Once again. I have not tested it.

David

NB: I had to re-format the SQL so I could read it. hehe

SELECT Bug.BugId,
P.FirstName AS PrimaryAgent,
temp.Closed,
temp.Status,
temp.Type,
temp.Priority,
temp.UserCode,
temp.Revision,
Enterer.FirstName AS EntererAgent
FROM Bug
INNER JOIN BugAgent ON BugAgent.BugId = Bug.BugId
INNER JOIN Agent ON BugAgent.AgentId = Agent.AgentId
INNER JOIN (
SELECT DISTINCT
Bug.BugId,
Bug.Closed,
BugStatus.Status,
BugType.Type,
Priority.Priority,
Customer.UserCode,
Revision.Revision
FROM Bug
INNER JOIN BugAgent ON BugAgent.BugId = Bug.BugId
INNER JOIN BugStatus ON BugStatus.BugStatusId = Bug.BugStatusId
INNER JOIN BugType ON BugType.BugTypeId = Bug.BugTypeId
INNER JOIN Priority ON Priority.PriorityId = Bug.PriorityId
INNER JOIN Customer ON Customer.CustomerId = Bug.CustomerId
LEFT JOIN BugRev ON BugRev.BugId = Bug.BugId
LEFT JOIN Revision ON Revision.RevisionId = BugRev.RevisionId
LEFT JOIN AgentCustomBugStatus ON Bug.BugStatusId = AgentCustomBugStatus.BugStatusId AND AgentId = 95 AND Active = 1
WHERE AgentCustomBugStatus.BugStatusId is null
AND BugAgent.AgentRoleId != 0
AND Bug.Closed = 0
AND BugAgent.AgentId = 95
GROUP BY Bug.BugId) AS temp ON Bug.BugId = temp.BugId
LEFT JOIN (
SELECT DISTINCT
Bug.BugId,
Agent.FirstName,
BugAgent.AgentRoleId
FROM Bug
INNER JOIN BugAgent ON Bug.BugId = BugAgent.BugId
INNER JOIN Agent ON BugAgent.AgentId = Agent.AgentId
LEFT JOIN AgentCustomBugStatus ON Bug.BugStatusId = AgentCustomBugStatus.BugStatusId AND AgentId = 95 AND Active = 1
WHERE AgentCustomBugStatus.BugStatusId is null
AND Bug.BugTypeId is not null
AND Bug.PriorityId is not null
AND Bug.CustomerId is not null
AND BugAgent.AgentRoleId = 6
AND Bug.Closed = 0 ) AS Enterer ON temp.BugId = Enterer.BugId
LEFT JOIN (
SELECT DISTINCT
Bug.BugId,
Agent.FirstName,
BugAgent.AgentRoleId
FROM Bug
INNER JOIN BugAgent ON Bug.BugId = BugAgent.BugId
INNER JOIN Agent ON BugAgent.AgentId = Agent.AgentId
LEFT JOIN AgentCustomBugStatus ON Bug.BugStatusId = AgentCustomBugStatus.BugStatusId AND AgentId = 95 AND Active = 1
WHERE AgentCustomBugStatus.BugStatusId is null
AND Bug.BugTypeId is not null
AND Bug.PriorityId is not null
AND Bug.CustomerId is not null
AND BugAgent.AgentRoleId != 6
AND Bug.Closed = 0) AS P ON temp.BugId = P.BugId
GROUP BY temp.BugId
ORDER BY Bug.BugTypeId ASC, Bug.Entered DESC