I use MySQL database with innoDB storage engine. I was trying to speed up the query for searching places by latitude and longitude and I found out that a search with smaller radius, less results performs slower than the one with more results. (latitude and longitude is the same, column ID is the primary key).
0.0011 sec, 15404 results
SELECT ID, 69.0 * DEGREES(ACOS(COS(RADIANS(51.5099983215332)) *
COS(RADIANS(Lat)) * COS(RADIANS(-0.05000000074505806) - RADIANS(Lng)) +
SIN(RADIANS(51.5099983215332)) * SIN(RADIANS(Lat)))) AS distance
FROM Table
WHERE (Lat BETWEEN 51.5099983215332 - (18 / 69.0)
AND 51.5099983215332 + (18 / 69.0)
AND Lng BETWEEN -0.05000000074505806 - (18 / (69.0 * COS(RADIANS(51.5099983215332))))
AND -0.05000000074505806 + (18 / (69.0 * COS(RADIANS(51.5099983215332)))) )
HAVING distance <= 18
0.0319 sec, 105 results
SELECT ID, 69.0 * DEGREES(ACOS(COS(RADIANS(51.5099983215332)) *
COS(RADIANS(Lat)) * COS(RADIANS(-0.05000000074505806) - RADIANS(Lng)) +
SIN(RADIANS(51.5099983215332)) * SIN(RADIANS(Lat)))) AS distance
FROM Table
WHERE (Lat BETWEEN 51.5099983215332 - (0.5 / 69.0)
AND 51.5099983215332 + (0.5 / 69.0)
AND Lng BETWEEN -0.05000000074505806 - (0.5 / (69.0 * COS(RADIANS(51.5099983215332))))
AND -0.05000000074505806 + (0.5 / (69.0 * COS(RADIANS(51.5099983215332)))) )
HAVING distance <= 0.5
Is that OK?
HAVING is applied after everything but ORDER BY and LIMIT, so narrowing it's condition can have marginal effects on query time. In this case, my guess would be (aside from normal performance variations) that it takes longer to discard 15299 results than it does to return them. I'd be curious to see if the performance difference evaporates if those queries are executed repeatedly with SQL_NO_CACHE.
To speed up the query it would make sense to add an index on lat, lng or lng, lat; the order won't really matter.