Map bounding box..

MySQL & MariaDB Other MySQL® Questions
1

Hi, I would really like any suggestions on the following concerning performance…:

I have 1.000.000 locations with:

  • a name
  • a latitude
  • a longitude
  • a score

I want to:

  • select all locations on a particular (google)map. So a latitude BETWEEN (… AND …) AND longitude BETWEEN (… AND …)
  • fulltext match name. So name MATCHES (…)
  • ORDER BY score

How would I get the best performance? What indexes should I create? Should I split the data in two tables and then JOIN? Or should I put everything together in one table?

ANY help would be great!!

2

1Million is not too much, depending on your traffic of course, you might simply be able to use MySQL Full Text Search plus extra filtering by distance.

If there are a lot of matches and they are filtered by distance match you’re stuck however.

Sphinx could be patched to do what you need to do really fast.

3

I did not know of Sphynx… it seems as a good (extra) solution to my problem.

I want to do something similar to the search on yelp.com or as is specified here.

At this time I’m using MySQL 4.1 and the following query:

SELECT SQL_CALC_FOUND_ROWS entries.*
FROM entries
JOIN geolocations ON (entries.id = geolocations.entry_id)
WHERE
( geolocations.wgs84_lat BETWEEN 55.9920055076675 AND 57.0005291105997 ) AND
( geolocations.wgs84_lon BETWEEN 3.55064105987549 AND 3.57124042510986 ) AND
MATCH (name) AGAINST (‘+name*’ IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 0, 8;

I also tried putting lat and lng in the entries table…but that was not helping very much…

Great forum BTW! Seems to be much more active than the original MySQL forum…

4

try not using “SQL_FOUND_CALC_ROWS” a friend of mine was using this with distance and it made the queries take a LOT longer than expected.

5

Does Sphinx allow you to use more FULLTEXT indexes? Or is it the same as with MySQL?

6

it allows you to use more than one index at a time. However it might not be how your thinking it would :wink:

7

With Sphinx you can build as many indexes as you want and also you can search only some of the columns.

It is however External search system

8

I was thinking of doing something like:

  • MATCH (colx, coly) AGAINST (‘+myname* +moreinfo*’ IN BOOLEAN MODE) (But this is als supported by MySQL I just found out!)
  • MATCH (colx) AGAINST (‘this’) AND MATCH (coly) AGAINST (‘+that*’ IN BOOLEAN MODE)

Or something…

I read everywhere that Sphinx is much better (read faster) in fulltext matching. Does it also support boolean matches?