mysql采用不同的执行时间

Question

SELECT user.name, user.age, user.lastlogin, user.loguserid,user.sex, ( 3963.17 * ACOS( COS( RADIANS( 54.662280100000) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( -1.630407900000) - RADIANS( longitude ) ) + SIN( RADIANS( 54.662280100000 ) ) * SIN( RADIANS( latitude ) ) ) ) AS distance_in_miles
FROM user
WHERE latitude != '' 
AND longitude != ''
AND loguserid !=526306 
AND age >18 AND age <=50 
AND  sex !=0
AND ( 3963.17 * ACOS( COS( RADIANS( 54.662280100000 ) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( -1.630407900000 ) - RADIANS( longitude ) ) + SIN( RADIANS( 54.662280100000) ) * SIN( RADIANS( latitude ) ) ) ) <=50 
AND NOT EXISTS (SELECT blockuser.userid
FROM blockuser 
WHERE blockuser.newuserid = user.loguserid 
AND blockuser.userid =526306 AND siteid =1071) 
AND NOT EXISTS (SELECT fav.userid FROM fav WHERE fav.favuserid =user.loguserid
AND siteid =1071 AND fav.userid =526306 ) ORDER BY `user`.`loguserid` DESC

Here is my sql?it always show different time of execution?any help would be appreciated.

Answer 1

MySql has a query cache. If the same statement is executed a second time, usually this takes only part of the time compared to the first execution, since the result can e taken from the query cache.

Once the cache has been invalidated / purged, it will again need a bit more time to execute.

Depending on what other clients on the database are doing, probably this statement waits for a release of a table lock which is needed for the sub-queries etc. There are precedences in the locks for SELECT, INSERT and UPDATE which probably apply.