How to simplify for speed

[Joshua Wilson]

I have this query that is listed below that takes forever to execute and then half the time its indexes get all messed up and doesn't return any data at all. Does anyone know how to simplify this and make it work easier and faster?


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, BugAgent, Agent, (
SELECT DISTINCT Bug.BugId, Bug.Closed, BugStatus.Status, BugType.Type, Priority.Priority,
Customer.UserCode, Revision.Revision
FROM Bug, BugAgent, BugStatus, BugType, Priority, Customer
LEFT JOIN BugRev ON BugRev.BugId = Bug.BugId
LEFT JOIN Revision ON Revision.RevisionId = BugRev.RevisionId
WHERE BugStatus.BugStatusId=Bug.BugStatusId
AND Bug.BugStatusId NOT IN (SELECT BugStatusId FROM AgentCustomBugStatus
WHERE AgentId=95 AND Active=1
)
AND BugAgent.AgentRoleId != 0
AND BugType.BugTypeId = Bug.BugTypeId
AND Priority.PriorityId = Bug.PriorityId
AND BugAgent.BugId = Bug.BugId AND Bug.Closed = 0
AND BugAgent.AgentId = 95
AND Customer.CustomerId = Bug.CustomerId
GROUP BY Bug.BugId
) AS temp
LEFT JOIN (
SELECT DISTINCT Bug.BugId, Agent.FirstName, BugAgent.AgentRoleId
FROM Bug, BugAgent, BugStatus, BugType, Priority, Customer, Agent
LEFT JOIN BugRev ON BugRev.BugId = Bug.BugId
LEFT JOIN Revision ON Revision.RevisionId = BugRev.RevisionId
WHERE BugStatus.BugStatusId=Bug.BugStatusId
AND Bug.BugStatusId NOT IN (SELECT BugStatusId FROM AgentCustomBugStatus
WHERE AgentId=95 AND Active=1
)
AND BugAgent.AgentRoleId = 6
AND Agent.AgentId = BugAgent.AgentId
AND BugType.BugTypeId = Bug.BugTypeId
AND Priority.PriorityId = Bug.PriorityId
AND BugAgent.BugId = Bug.BugId AND Bug.Closed = 0
AND Customer.CustomerId = Bug.CustomerId
) AS Enterer ON temp.BugId = Enterer.BugId
LEFT JOIN (
SELECT DISTINCT Bug.BugId, Agent.FirstName, BugAgent.AgentRoleId
FROM Bug, BugAgent, BugStatus, BugType, Priority, Customer, Agent
LEFT JOIN BugRev ON BugRev.BugId = Bug.BugId
LEFT JOIN Revision ON Revision.RevisionId = BugRev.RevisionId
WHERE BugStatus.BugStatusId=Bug.BugStatusId
AND Bug.BugStatusId NOT IN (SELECT BugStatusId FROM AgentCustomBugStatus
WHERE AgentId=95 AND Active=1
)
AND BugAgent.AgentRoleId != 6
AND Agent.AgentId = BugAgent.AgentId
AND BugType.BugTypeId = Bug.BugTypeId
AND Priority.PriorityId = Bug.PriorityId
AND BugAgent.BugId = Bug.BugId AND Bug.Closed = 0
AND Customer.CustomerId = Bug.CustomerId
) AS P ON temp.BugId = P.BugId
WHERE Bug.BugId = temp.BugId
AND BugAgent.BugId = Bug.BugId
AND BugAgent.AgentId = Agent.AgentId
GROUP BY temp.BugId ORDER BY Bug.BugTypeId ASC, Bug.Entered DESC