CODEWITHSUNDEEP
mysql
Lee L
Lee L

Reputation: 201

MySQL Query Takes A Long Time To Find POIs by Latitude and Longitude

I have 17 million points of interest in a MySQL table (v5.0.77), with several fields, including name,lat,lng, and category. Lat and Long are of type Decimal(10,6), and Category is a Small Integer. I have an multi-column index on lat,lng,category.

My queries to find points within 2km of location take a long time - on average about 120 seconds.

If I query from exactly the same center point, I can tell that the query is cached b/c the query executes in less than second. As soon as I change the center point, the query takes a long time again.

I do my calculation to determine the bounds of the area I'm searching outside of the query, versus a distance calculation within it, which is the source of a lot of reports you see about similar queries taking a long time.

Here's an example from the Slow Query Log:

Query_time: 177  Lock_time: 0  Rows_sent: 2841  Rows_examined: 28691

SELECT p.id, p.name AS name, p.lat, p.lng, c.name AS category
FROM poi AS p 
LEFT JOIN categories AS c ON p.category = c.id
WHERE p.lat BETWEEN 37.524993 AND 37.560965 AND p.lng BETWEEN -77.491776 AND -77.446408;

I feel like the server is tuned correctly - I have enough memory, it's just me using it for development, I feel I've tweaked MySQL settings appropriately.

This has really stumped me for a while now. Shouldn't MySQL be able to very efficiently scan the index I've created? Should I convert to spatial data types, or use Sphinx to improve query speed? Any thoughts/perspective much appreciated.

Upvotes: 0

Views: 251

Answers (2)

josegil
josegil

Reputation: 365

Have you tried to use the spacial extension in mysql (http://dev.mysql.com/doc/refman/5.1/en/spatial-extensions.html)? I think that you can get better performance in your database if you use the date type "geometry" as and index and search using the rectangle created by the latitude-longitude. (info about the type geometry http://dev.mysql.com/doc/refman/5.0/en/geometry-property-functions.html).

I´ve used it with a database with 150k. places and the query responds in few miliseconds.

Upvotes: 1

yurisich
yurisich

Reputation: 7119

This might seem extreme, but you could hard code logic into your inserts, updates and retrieval procedures to look at the category field, and select the table that matches the category type you're looking for. Yes, that means you'll have tables dedicated specifically for a certain category, and this may come off as too heavy handed for most, and complicate maintenance later. But if your categories are not modified often (GPS coordinates don't strike me as something that will change anytime soon), you might want to consider it.

Upvotes: 0

Related Questions