Re: Adding index kills query

[Øystein Grøvlen]

Hi,

It seems I over-looked that the EXPLAIN shows that the optimizer selects the idxComp index instead of the covering index idxCompPurchAmt. This will be much slower than a table scan since it means that both the whole index and the whole table must be read, and the table will not be read in sequential order like with a table scan. I suspect this is a bug in MySQL 5.5 that is may be fixed in later versions. (There are several bugs in MySQL 5.5 wrt choosing index to avoid sorting).

If my suspicion is correct, the following query should give better performance:

SELECT iCompanyID, SUM(fPurchaseAmt)
FROM Trans_smp
FORCE INDEX (idxCompPurchAmt)
GROUP BY iCompanyID;

However, I suggest you also upgrade to a newer version of MySQL.


Hope this helps,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway