Adding index kills query

[Robert Stafford]

I am trying to sum a variable, fPurchaseAmt, which is a float, grouped by iCompanyID, which is a BigInt. Adding an index on iCompanyID seems to have killed the performance. My table, Trans_smp, has 15 million rows, and about 10 other columns. The engine is InnoDB. In an effort to improve performance, I added an index on iCompanyID and another variable, iBrandID:

create index idxCompBrand on Trans_smp (iCompanyID, iBrandID);

No problems. I ran the following test code before and after creating the index:

select iCompanyID, count(fPurchaseAmt) FROM Trans_smp GROUP BY iCompanyID;

Before creating the index, it ran in about 34 seconds, after creating the index, it ran in about 14 seconds.

I then ran the code I want:

select iCompanyID, sum(fPurchaseAmt) FROM Trans_smp GROUP BY iCompanyID;

Before creating the index, it ran in about 33 seconds, roughly the same as the count. After creating the index, it either fails or it takes a long time. The most recent time I tried it, I waited 4 hours and it didn't finish. In an effort to get it to work, I tried creating a second index, limited just to iCompanyID:

create index idxComp on Trans_smp (iCompanyID);

After creating the second index, I tried again and it didn't finish in less than 4 hours, so I pulled the plug.

After creating the second index, I tried again with the following code:

select iCompanyID, sum(fPurchaseAmt)
FROM Trans_smp
ignore index idxCompBrand
ignore index idxComp
GROUP BY iCompanyID
;

Once again, ran without problems in about 33 seconds.

At someone's suggestion, I also tried creating an index on iCompanyID and fPurchaseAmt:

create index idxCompPurchAmt on Trans_smp (iCompanyID, fPurchaseAmt);

This also did not help - after several hours, I killed the sum query before it finished.

Anyone have any ideas about what my problem is, and how to avoid it? Obviously I can just use the "ignore index" approach, but I hope to start using this and similar steps on the full set, with has 350 million rows, so I would like to be able to use indexes.

Here's the full results for "show create table":

create table Trans_smp (
aTrans BigInt bigint(20) unsigned not null default '0'
, iCustID BigInt(20) unsigned default null
, iChainID SmallInt(5) unsigned default null
, iDeptID SmallInt(5) unsigned default null
, iCategoryID BigInt(20) unsigned default null
, iCompanyID BigInt(20) default null
, iBrandID BigInt(20) unsigned default null
, dTransDt date default null
, fProductSz float default null
, cProductMeasure char(8) default null
, iPurchaseQty MediumInt(9) default null
, fPurchaseAmt float default null
, key idxCompBrand (iCompanyID, iBrandID)
, key idxComp (iCompanyID)
, key idxCompPurchAmt (iCompanyID, fPurchaseAmt)
)
engine=InnoDB default charset=latin1;