Most efficient MySQL solution for querying against coordinates

Question

I have a database of geolocalized objects:

id
latitude
longitude

I am querying the database to fetch objects that are inside a box:

WHERE longitude >= - 33.83789
  AND longitude <= 33.837891
  AND latitude >= 4.214943
  AND latitude <= 40.979898

There is one index on: latitude, longitude.

Is there a more efficient way of storing/querying the database? Right now, depending of the box coordinates/objects density, query times vary from 0.1s to more than 1s.

I know about MySQL Spacial extension, but it seems a bit of work to use and I have no idea if it will be easier/more efficient.

Additional info: I am also planning of performing simple distance-based queries (fetch objects near a specified location) in the future.

@nawfal I agree it would at least look better, I didn't bother — Matthieu Napoli
– Matthieu Napoli, Commented Aug 31, 2012 at 22:24

Mark Byers · Accepted Answer · 2012-08-31 22:15:42Z

2

Is there a more efficient way of storing/querying the database?

Absolulely, yes! Spatial extensions are exactly designed to solve this problem.

I know about MySQL Spacial extension, but it seems a bit of work to use and I have no idea if it will be easier/more efficient.

It's not easier (but it's not much harder), and it is more efficient. For large data sets it can be a lot more efficient.

answered Aug 31, 2012 at 22:15

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

3 Comments

Matthieu Napoli Over a year ago

Oh I wasn't expecting it would be that much of an improvement for the performances, great! Just one question, is the SPATIAL index type a part of the MySQL Spatial extension? Or is the Spatial extension something different? Because the SPATIAL index seems interesting in my case, but I don't want to miss out (again) on the MySQL spatial extension.

Mark Byers Over a year ago

@Matthieu: The SPATIAL index is the magic that makes it faster. You will also need to change your query to use the one of the spatial functions.

okainov Over a year ago

Would you be able to provide some example how should the DB administrator introduce and use those indexes?

Collectives™ on Stack Overflow

Most efficient MySQL solution for querying against coordinates

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related