MySQL Forums
Forum List  »  Performance

How to simplify for speed
Posted by: Joshua Wilson
Date: February 15, 2005 04:22PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
How to simplify for speed
2257
February 15, 2005 04:22PM
1764
February 15, 2005 11:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.