slow performance / index not used
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?