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]
