MySQL can use the index on one of the columns in a multi-column index,
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
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:
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.
Mikhail Berman wrote:
>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
>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:
>>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
>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.
> 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.