MySQL Forums :: Performance :: 11 millions records : more than 30 seconds to retrieve simple request


Advanced Search

11 millions records : more than 30 seconds to retrieve simple request
Posted by: Funkiki ()
Date: January 03, 2011 10:12AM

Hi

I have a doubt about MySQL performance on large dataset

Hereafter my simple test table

`variable` varchar(80) NOT NULL,
`log_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`milli` int(10) unsigned NOT NULL DEFAULT '0',
`num_value` double NOT NULL,
PRIMARY KEY (`variable`,`log_time`,`milli`) USING BTREE,
The engine is MyISAM. MySQL version is 5.5.8

I'm using the MySQL Query browser tool to make the performances tests.
The table contains 11 millions records.

When I run the following request :
SELECT * FROM my_table WHERE variable='a variable name' AND log_time >= '2010-10-11' AND log_time < '2010-10-25'
it takes more than 30 seconds to get the result that contains about 60000 records.
(of course, then, if I execute the request again, the answer is immediate , because of caching).

Now, if I do the following request :
SELECT variable,log_time,milli FROM my_table WHERE variable='another variable name' AND log_time >= '2010-10-11' AND log_time < '2010-10-25',
the result is less than 1second, but of course, the 'num_value' is missing.
If I run the same request with adding the 'num_value' field in the SELECT statement, then the running time is again about 30 seconds (and more)

Have you got an explanation about this ?

Why the first request takes so much time ?

Maybe the request with 'SELECT variable,log_time,milli' is very fast, because all the fields are part of the PRIMARY key and the process doesn't need to extract more data elsewhere ?
I'm currently rebuilding my table by adding the num_value field as member of the PRIMARY key to check that hypothesis. But, of course, this is not the good solution, because I don't want to make all the table fields part of the PRIMARY key, which would be stupid !
This is now more than 2 hours that the table is rebuilding... I will give you the result of this test when it will be finished.

But, I would like some clarification about that first test.

BR

Options: ReplyQuote


Subject Views Written By Posted
11 millions records : more than 30 seconds to retrieve simple request 3226 Funkiki 01/03/2011 10:12AM
Re: 11 millions records : more than 30 seconds to retrieve simple request 2076 Rick James 01/04/2011 10:45PM
Re: 11 millions records : more than 30 seconds to retrieve simple request 1258 Funkiki 01/05/2011 05:09AM
Re: 11 millions records : more than 30 seconds to retrieve simple request 1803 Rick James 01/05/2011 08:53AM
Re: 11 millions records : more than 30 seconds to retrieve simple request 2198 Funkiki 01/06/2011 02:30PM
Re: 11 millions records : more than 30 seconds to retrieve simple request 960 Rick James 01/06/2011 08:54PM
Re: 11 millions records : more than 30 seconds to retrieve simple request 1536 Funkiki 01/07/2011 04:16AM
Re: 11 millions records : more than 30 seconds to retrieve simple request 854 Rick James 01/07/2011 10:06PM
Re: 11 millions records : more than 30 seconds to retrieve simple request 1434 Funkiki 01/14/2011 11:32AM
Re: 11 millions records : more than 30 seconds to retrieve simple request 907 Rick James 01/15/2011 01:25AM


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.