Help with querying large table.

[Brent Bigler]

The table holds about 25 million records now, and will get larger. I'm having trouble figuring out how to produce an acceptably fast response to this query:

SELECT sum(h_shares) as shares, h_co_id FROM holdings where h_cusip = 'xxxxxxxxx' group by h_co_id order by shares DESC

(The point of the above query is to find all asset management companies holding a certain security in order of number of shares.)

Explain produces this reponse:

select_type: SIMPLE
table: holdings
type: ref
possible_keys: cusip
key: cusip
key_len: 9
ref: const
rows: 8180 (or whatever)
Extra: Using where; Using temporary; Using filesort

The table structure looks like this:

CREATE TABLE `holdings` (
`h_id` int(10) unsigned NOT NULL auto_increment,
`h_co_id` int(10) unsigned NOT NULL default '0',
`h_filingid` int(10) unsigned NOT NULL default '0',
`h_cusip` varchar(12) NOT NULL default '0',
`h_security_name` varchar(150) NOT NULL default 'nulltext',
`h_value` double NOT NULL default '0',
`h_shares` double NOT NULL default '0',
`h_security_type` tinyint(3) unsigned NOT NULL default '0' COMMENT '1=stock,2=bond,3=other',
PRIMARY KEY (`h_id`),
KEY `shares` (`h_shares`),
KEY `cusip` USING BTREE (`h_cusip`(9),`h_co_id`),
KEY `value` (`h_value`),
KEY `co_id` USING BTREE (`h_co_id`,`h_cusip`(9)),
KEY `filingID` USING BTREE (`h_filingid`,`h_co_id`),
KEY `filing_date` (`h_date_report`),
KEY `security_type` (`h_security_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I'd appreciate any guidance at all!

Thanks.

--Brent