3

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.

Matthieu Napoli
  • 48,448
  • 45
  • 173
  • 261

1 Answers1

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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 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. – Matthieu Napoli Aug 31 '12 at 22:22
  • @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](http://dev.mysql.com/doc/refman/5.6/en/functions-for-testing-spatial-relations-between-geometric-objects.html). – Mark Byers Aug 31 '12 at 22:23
  • Would you be able to provide some example how should the DB administrator introduce and use those indexes? – The Godfather Aug 24 '18 at 13:53