Help with querying large table.
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