List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 18 2005 5:10pm
Subject:Re: A "key" question
View as plain text  
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