MySQL Forums
Forum List  »  Performance

Re: Help with querying large table.
Posted by: Brent Bigler
Date: October 10, 2005 12:07AM

After working on this a long time, it seems the best workaround was just to add a separate internal numeric id field for each cusip. This (original) query usually takes about three minutes:

select sum(shares) shares from holdings where cusip ='xxxxxxxxx' group by co_id order by shares DESC

By contrast, this (new) query takes a few seconds:

select sum(shares) shares from holdings where cusip_id = 5665 group by co_id order by shares DESC

It seems the only difference lies in the field types: cusip is a VARCHAR field with a 9 digit index; cusip_id is an integer field indexed normally.

Of course, there's a separate table that holds the cusip and cusip_id fields...

Thanks for your help!

--Brent

Options: ReplyQuote


Subject
Views
Written By
Posted
1912
October 04, 2005 10:04PM
1321
October 05, 2005 01:04AM
1495
October 05, 2005 06:41AM
1249
October 05, 2005 07:28PM
Re: Help with querying large table.
1281
October 10, 2005 12:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.