Michael,
Thank you for your comments.
This give me a new ideas how to work with this issues.
And, no at this point we are not planning to work with price_data_ticker
field itself.
Regards,
Mikhail Berman
-----Original Message-----
From: Michael Stassen [mailto:Michael.Stassen@stripped]
Sent: Friday, November 18, 2005 12:11 PM
To: Mikhail Berman
Cc: Jeremy Cole; Jasper Bryant-Greene; mysql@stripped
Subject: Re: A "key" question
Mikhail Berman wrote:
> Dear Jeremy,
>
> Thank you for your help.
>
> I do have an exact situation you have assume I have. Here is the
> output of SHOW CREATE TABLE
>
CREATE TABLE `TICKER_HISTORY_PRICE_DATA` (
> `price_data_ticker` char(8) NOT NULL default '',
> `price_data_date` date NOT NULL default '0000-00-00',
> `price_data_open` float default NULL,
> `price_data_high` float default NULL,
> `price_data_low` float default NULL,
> `price_data_close` float default NULL,
> `price_data_volume` float default NULL,
> UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
>
> As you can see, Unique KEY is on two first fields, but most of the
> work, joins & searches, will be done on the second field
"price_data_date".
As others have pointed out, your UNIQUE KEY on
(price_data_ticker,price_data_date) will serve as an index to speed
queries which search for a specific value of price_data_ticker and
queries which search for a specific combination of values of
price_data_ticker and price_data_date, but it won't help queries which
only search by price_data_date. Yet, "most of the work, joins &
searches, will be done on the second field, price_data_date."
In that case, you definitely need an index on price_data_date. Based
on your description, I'd suggest you have your index backwards. What
you need is an index on (price_data_date, price_data_ticker). This will
satisfy searches on price_data_date and on combinations of the two.
Hence,
ALTER TABLE TICKER_HISTORY_PRICE_DATA
DROP INDEX tidadx,
ADD PRIMARY KEY (price_data_date, price_data_ticker);
That will satisfy most of your queries. Then, the question becomes, do
you need a separate, single-column index on price_data_ticker? That
will depend on whether you run queries which select based on
price_data_ticker without specifying price_data_date.
Michael
