More results - faster query (search by latitude and longitude in mysql)

Question

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?

both queries: id='1',select_type='SIMPLE', table='Table', type='ALL', possible_keys=NULL, key=NULL, key_len=NULL, ref=NULL, rows='57349', Extra='Using where' (but the number of 'rows' is different on every refresh, similar figures in both queries) — Bajlo
– Bajlo, Commented Feb 18, 2016 at 17:54
so you dont have a composite index on (lat,Lon) . Create the index over both Columns. It will speed up both querys — Bernd Buffen
– Bernd Buffen, Commented Feb 18, 2016 at 18:31

Uueerdo · Accepted Answer · 2016-02-18 17:51:54Z

1

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.

answered Feb 18, 2016 at 17:51

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Uueerdo Over a year ago

@RickJames since the query is using a WHERE with an effectively precomputed approximated square region, that portion should be able to take advantage of the index; but you are correct that the precise distances still need calculated on all the rows in that approximation region for the final culling.

Rick James Over a year ago

Ah! With the wrapping, I can see that now. However, it will only use lat or lng, never both of them. If he got the ids from the WHERE, then self-joined, there would be fewer distances to calculate.

Bajlo Over a year ago

Thanks, the index on Lat and Lng has speeded up the query. (SQL_NO_CACHE didn't make any difference)

Uueerdo Over a year ago

@Bajlo In my experience, SQL_NO_CACHE makes very little difference in most queries; the reason I suggested it was not for speeding things up, but from reducing caching benefits to see the true performance difference between "fresh" executions of both queries. (You'll notice with many queries, without SQL_NO_CACHE, you can run the query unmodified consecutive times and have much much much better speed than the initial query that did the actual work.)

Collectives™ on Stack Overflow

More results - faster query (search by latitude and longitude in mysql)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related