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:[EMAIL PROTECTED]
Sent: Friday, November 18, 2005 12:11 PM
To: Mikhail Berman
Cc: Jeremy Cole; Jasper Bryant-Greene; mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
