MySQL Performance: MySQL => Why second run of same query is much faster (no query-cache)

Members

Help

Home

Home » Performance » MySQL » Why second run of same query is much faster (no query-cache)

Show: Today's Messages :: Show Polls :: Message Navigator

Why second run of same query is much faster (no query-cache) [message #1133]

Mon, 23 April 2007 04:52

erwin_se
Messages: 1
Registered: April 2007

Junior Member

I was wondering why the same query takes +/- 12 seconds the first time, but only milliseconds the second time.

- I have query-cache disabled.
- It is still true after I have restarted MySQL service (windows).

The query is about comparing one small table (loc_0) containing +/- 750 rows with a big table (location) containing 110M rows.

The big table is compressed, 790 MB big with an index of size 4.4GB.

I was wondering that on any hardware configuration (8 GB RAM?), I may always achieve the millisecond performance. Would that be possible?

This is the actual query:

SELECT location.sequenceid AS RSequenceid,
loc_0.sequenceid AS LSequenceid,
loc_0.seq_region_id AS seq_region_id,
loc_0.location AS location
FROM loc_0,location
WHERE
location.location=(loc_0.location+12)
AND location.seq_region_id=loc_0.seq_region_id;

Report message to a moderator

Re: Why second run of same query is much faster (no query-cache) [message #1136 is a reply to message #1133 ]

Mon, 23 April 2007 08:37

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

My guess is that main performance is due to OS file cache.

All operating system is using spare RAM for file cache to avoid disk seeks/writes.

And even if you restart the service the entire file will probably still be accessible in the file cache. Unless you have a lot of other processes reading a lot of data from disk which will cause the OS to remove the old files from the file cache to replace with more recently requested files.

Try rebooting your server and check the execution time.
Then you will start to get figures that should hopefully be fairly repeatable.

Report message to a moderator

Previous Topic:	Using transaction in C# lock time out problem
Next Topic:	dedicated DBServer performance

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 9 23:01:57 EDT 2009

Total time taken to generate the page: 0.01172 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.