Query taking a lot of time if there is no record in table

[Aditi mittal]

Hi,

I am having a table rate_history which is having 57,939,293 records.

The problem is when we are trying to fetch a record which is not in database it took a lot of time (approximately 10 to 17 minutes) which hangs the site.


The Query we are executing: SELECT BID as rate, RATE_TIME as time FROM RATE_HISTORY WHERE PAIR = 'EURUSD' AND DATE(RATE_TIME) = '2008-11-01' ORDER BY RATE_TIME DESC LIMIT 1;

The table structure of table is
CREATE TABLE `rate_history` (
`RATE_HISTORY_ID` int(11) NOT NULL auto_increment,
`PAIR` varchar(6) collate utf8_unicode_ci NOT NULL,
`BID` decimal(12,6) NOT NULL,
`ASK` decimal(12,6) NOT NULL,
`LOW` decimal(12,6) NOT NULL,
`HIGH` decimal(12,6) NOT NULL,
`RATE_TIME` datetime NOT NULL,
PRIMARY KEY (`RATE_HISTORY_ID`),
KEY `RATE_HISTORY_I_1` (`PAIR`,`RATE_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


There are two indexes : RATE_HISTORY_ID and (PAIR,RATE_HISTORY_ID)

Can anyone tell How we can solve this problem or what we are doing wrong.

Thanks for any help.