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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
