List:General Discussion« Previous MessageNext Message »
From:Mikhail Berman Date:November 18 2005 6:51pm
Subject:RE: A "key" question
View as plain text  
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


Thread
A "key" questionMikhail Berman17 Nov
  • Re: A "key" questionJasper Bryant-Greene17 Nov
  • Re: A "key" questionPeter Brawley17 Nov
RE: A "key" questionMikhail Berman17 Nov
  • Re: A "key" questionJeremy Cole17 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionJeremy Cole18 Nov
  • Re: A "key" questionMichael Stassen18 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionMichael Stassen18 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionDavid Griffiths18 Nov
  • Connection ProblemTripp Bishop18 Nov
    • Re: Connection ProblemMichael Stassen18 Nov
      • Re: Connection ProblemTripp Bishop18 Nov