Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to sort database rows according to GPS coordinates (or any other geographical representation).

The way I've understood it, MySQL Spatial Indexes are too buggy at the time of writing (at least articles and my own tests indicate so).

So I can either switch to PostgreSQL to use PostGIS which seems to be working excellently, or I can do it mathematically in MySQL:

((acos(sin(latitude * pi() / 180) * sin($latitude * pi() / 180) + cos(latitude * pi() / 180) * cos($latitude * pi() / 180) * cos((longitude - $longitude) * pi() / 180)) * 180 / pi()) * 60 * 1.1515 * 1.609344) km

I'm not asking for a PostgreSQL vs MySQL debate, but simply how the performance of the MySQL expression scales as rows grow compared to using PostGIS/PostgreSQL.

It's a drastic step to switch database system, so the upside better be significant.

share|improve this question

closed as too broad by dezso, Jon Seigel, Max Vernon, Paul White, bluefeet Oct 6 '13 at 11:25

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
The only way to answer this question is to run benchmarks with your load and your data. –  a_horse_with_no_name Aug 4 '13 at 8:33
    
Really? So ordering by manual calculations in MySQL could just as well be as fast as built-in spatial indexes? –  forthrin Aug 4 '13 at 10:21
    
No, for MySQL I suggest you use spatial indexes for finding points nearby. No enhancements or improvements on math functions can be faster than spatial indexes. –  ypercube Aug 4 '13 at 11:23
    
@ypercube: MySQL Spatial Indexes seem buggy and unusable at the time of writing. Do you know when they will be at a mature and usable state? –  forthrin Aug 4 '13 at 14:06
    
They certainly do not provide all the functionality of PostGIS. But what bug have you hit? Perhaps you can write a question with the design and the query you used and someone may help with the specific efficiency/problems that showed up. –  ypercube Aug 4 '13 at 14:12

1 Answer 1

There are a couple of reasons I would expect PostgreSQL to perform better here.

The first is that you can index the output of a function in PostgreSQL. If you have complex calculations, you can index data related to that, derivative of data you are actually storing.

The second is that spacial indexes are very mature in PostgreSQL. This allows very complex calculations, such as overlapping of bounding boxes, to be done using indexes.

There is an exception however, in that certain scenarios might do better on MySQL, for example if you are mostly running the same exact query over and over again.

share|improve this answer

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