Why second run of same query is much faster (no query-cache)

MySQL & MariaDB Other MySQL® Questions
1

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;

2

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.