Map bounding box..

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!!

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.

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…

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.

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

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

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

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?