3

I have a database with > 7.5 million rows (and growing), each with an image record that has a specific LAT/LNG geolocation representing where the photo was taken, stored as DECIMAL values -

 mysql> describe image_meta;
 +---------------------+------------------+------+-----+-------------------+----------------+
 | Field               | Type             | Null | Key | Default           | Extra          |
 +---------------------+------------------+------+-----+-------------------+----------------+
 | id                  | int(11) unsigned | NO   | PRI | NULL              | auto_increment |
 | media_id            | varchar(255)     | YES  | UNI | NULL              |                |
 | user_id             | int(11) unsigned | YES  | MUL | NULL              |                |
 | create_time_unix    | int(11) unsigned | YES  | MUL | NULL              |                |
 | create_time_zulu    | datetime         | YES  |     | NULL              |                |
 | latitude            | decimal(12,10)   | YES  | MUL | NULL              |                |
 | longitude           | decimal(13,10)   | YES  | MUL | NULL              |                |

I want to search within a rectangular area, defined by LAT/LNG, and the queries are very slow.

I am using this query:

SELECT user_id FROM image_meta WHERE (latitude BETWEEN 40.779769 AND 40.792399 AND longitude BETWEEN -73.988457 AND -73.963308);

with the result:

123569 rows in set (8 min 28.99 sec)

What's the best way to go about making this go faster? Should I search a broader area with fewer significant figures, then use PHP to narrow the results by the more precise LAT/LNG boundaries?

I'm a novice SQL guy (always had DBAs to help me in the past) but I'm doing this project solo. Thanks in advance...

1 Answer 1

1

"Standard" MySQL indexes use B-Trees, which are horrible for searching ranges in 2 or more dimensions (they can only use the first column). You want to use R-Trees, available, like in many other RDBMS, when using the GIS/spatial extensions for MySQL.

They require specific datatypes. POINT is what you want- you will have to convert the latitude and the longitude to this format. Then create a spatial index. Then using the function Contains() or MBRContains().

Please note that this feature is typicaly criticised in MySQL for the lack of features (for example, it wasn't available for InnoDB before 5.7, no distance function, no projection support, ...). Ironically, the lack of those features made it a very fast implementation -when it worked. And it should be enough for what you want to do.

If you cannot use spatial indexing in MySQL, an index on (latitude, longitude) may be a bit faster in 5.6 with ICP, or you can alternatively try to use some indexing tricks. If that does not work for you, and it is still too slow, you will need an external indexing solution or a complete change of database server.

1
  • working on implementing this solution. Everything seems to be going fine, except the query to actually fetch the data. I've posted a follow up question here-dba.stackexchange.com/questions/71573/… and would love for you to chime in if you have time. Jul 15, 2014 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.