List:General Discussion« Previous MessageNext Message »
From:Jeremy Cole Date:November 18 2005 4:00pm
Subject:Re: A "key" question
View as plain text  
Hi Mikhail,

> Thank you for your help.
> 
> I do have an exact situation you have assume I have. Here is the output
> of SHOW CREATE TABLE
> 
>   `price_data_ticker` char(8) NOT NULL default '',
>   `price_data_date` date NOT NULL default '0000-00-00',

  ...

>   UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`)

  ...

> 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".

Could you provide some example queries?

Likely the solution is to create another index on price_data_date, that 
could be used for searches by date that do not include ticker.

As I mentioned before, an index on (a, b) can be used for (a) but not 
for (b) alone.  However, it usually doesn't make sense to create an 
index on (b, a) as well, since if you have both columns in your query, 
usually the index on (a, b) would be fine.  So I would suggest adding an 
index:

   ALTER TABLE `TICKER_HISTORY_PRICE_DATA`
     ADD INDEX (price_data_date);

Keep in mind that will lock the table to add the index, and may take a 
few minutes (although I would expect less than two minutes for 32M rows) 
so it might not be a good idea to run while the market is open. :)

If you could provide the exact query you were running, I could confirm 
that it would or would not help. :)

Regards,

Jeremy

-- 
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104
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