List:General Discussion« Previous MessageNext Message »
From:David Griffiths Date:November 18 2005 7:22pm
Subject:Re: A "key" question
View as plain text  
MySQL can use the index on one of the columns in a multi-column index, 
with caveats.

If this is your index,

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

and you plan to use "price_data_date" in all your queries, but never "price_data_ticker",
then simply reverse the order of the columns in your index definition:

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


If you have a composite index on columns a, b and c:

create index a_b_c_idx ON table_name (a, b, c);

and you query with "a" in the where clause, the composite index will be 
used.

If you query with "a" and "b" in the where clause, the composite index 
will be used; ditto for "a", "b" and "c".

But if you query with "b" (and only "b") in the where clause, the index 
won't be used.

If you use "b" and "c" in the where clause, the index won't be used.

Look here for other examples:
http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html

Don't add indexes you don't need - it slows down inserts (and updates 
where the indexed columns are being updated), uses up space in your 
database, and requires extra administration, etc.

David

Mikhail Berman wrote:

>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