MySQL Forums
Forum List  »  MyISAM

slow performance / index not used
Posted by: Alex Nguyen
Date: July 05, 2006 12:21PM

I'm running version 5.0.20 and here's the query:

SELECT a.ApplicationID, a.TransactionName, count(*) trxCount, b.Total, (count(*)/b.Total * 100) Pct_Success, avg(a.Duration), min(a.Duration), max(a.Duration)
FROM csiaudit a,
(SELECT ApplicationID, TransactionName, count(*) Total
FROM csiaudit
WHERE StartTime > "2006-07-05 07:00:00"
AND StartTime < "2006-07-05 23:00:00"
GROUP BY ApplicationID, TransactionName) as b
WHERE a.StartTime > "2006-07-05 07:00:00"
AND a.StartTime < "2006-07-05 23:00:00"
AND a.TransactionStatus = "COMPLETE"
AND a.ApplicationID = b.ApplicationID AND a.TransactionName = b.TransactionName
GROUP BY a.ApplicationID, a.TransactionName;

The problem I'm running into is that eventhough there's an index on the StartTime column, the query is taking forever to complete (~30secs with ~3.5 million rows). The column StartTime is a datetime datatype. If I run the nested SELECT separately, it uses the index. If I remove the nested SELECT, the main query uses the index. Any ideas or a better way to rewrite the query?

Options: ReplyQuote


Subject
Views
Written By
Posted
slow performance / index not used
3646
July 05, 2006 12:21PM
1831
July 14, 2006 12:07PM
1626
July 19, 2006 01:27AM


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.