Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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.

    Comment

    Working...
    X