MySQL Forums
Forum List  »  Performance

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


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.


Options: ReplyQuote

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.