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]
