MySQL Forums :: Optimizer & Parser :: Query taking a lot of time if there is no record in table


Advanced Search

Query taking a lot of time if there is no record in table
Posted by: Aditi mittal ()
Date: April 29, 2009 04:42AM

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.

Options: ReplyQuote


Subject Views Written By Posted
Query taking a lot of time if there is no record in table 4222 Aditi mittal 04/29/2009 04:42AM
Re: Query taking a lot of time if there is no record in table 2051 Rick James 04/30/2009 11:31PM
Re: Query taking a lot of time if there is no record in table 2042 Aditi mittal 05/04/2009 01:30AM
Re: Query taking a lot of time if there is no record in table 1991 Rick James 05/04/2009 07:19PM
Re: Query taking a lot of time if there is no record in table 2055 Aditi mittal 05/04/2009 11:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.