mysql不稳定_MySQL不稳定查询时间

I am using MySQL version 5.5.14 to run the following query from a table of 5 Million rows:

SELECT P.ID, P.Type, P.Name, P.cty

, X(P.latlng) as 'lat', Y(P.latlng) as 'lng'

, P.cur, P.ak, P.tn, P.St, P.Tm, P.flA, P.ldA, P.flN

, P.lv, P.bd, P.bt, P.nb

, P.ak * E.usD as 'usP'

FROM PIG P

INNER JOIN EEL E

ON E.cur = P.cur

WHERE act='1'

AND flA >= '1615'

AND ldA >= '0'

AND yr >= (YEAR(NOW()) - 100)

AND lv >= '0'

AND bd >= '3'

AND bt >= '2'

AND nb <= '5'

AND cDate >= NOW()

AND MBRContains(LineString( Point(-65.6583, -87.8906)

, Point(65.6583, 87.8906)

), latlng)

AND Type = 'g'

AND tn = 'l'

AND St + Tm - YEAR(NOW()) >= '30'

HAVING usP BETWEEN 300/2 AND 300 LIMIT 100;

On the first occasion, it took 313s, on the second occasion it took 48s and on the third occasion it took 101s. From explain select, the query plan has type: range; key: index and extra: using where on table P followed by type: eq_ref and key: primary on table E. This query does not use query cache. My question is: "Why is the query time so dramatically different?"

Cheers,

Ben

解决方案

Use SQL_NO_CACHE when profiling queries:

SELECT SQL_NO_CACHE P.ID, P.Type, ...

The reason it takes less times on the second run is that the result of the query has been cached by MySQL.

SQL_NO_CACHE disables caching for this query. You can clear the query cache with FLUSH QUERY CACHE.