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