Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » Map bounding box..
Map bounding box.. [message #517] Mon, 01 January 2007 13:50 Go to next message
oordopjes is currently offline oordopjes
Messages: 15
Registered: January 2007
Junior Member
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!!
Report message to a moderator
Re: Map bounding box.. [message #518 is a reply to message #517 ] Mon, 01 January 2007 14:47 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: Map bounding box.. [message #519 is a reply to message #518 ] Mon, 01 January 2007 18:10 Go to previous messageGo to next message
oordopjes is currently offline oordopjes
Messages: 15
Registered: January 2007
Junior Member
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..
Report message to a moderator
Re: Map bounding box.. [message #520 is a reply to message #519 ] Mon, 01 January 2007 18:20 Go to previous messageGo to next message
oordopjes is currently offline oordopjes
Messages: 15
Registered: January 2007
Junior Member
Ooh.. and I've tried almost any index possible.. but maybe someone can point out which should be the best to create.

And I was wondering if keeping the tables separate would gain performance..since then the
- geolocations table can use a lat,lng(,entry_id) index
- entries table can use the fulltext index
and those are JOINed..

But what is the best way and why are my queries taking up to 20seconds?
Report message to a moderator
Re: Map bounding box.. [message #521 is a reply to message #520 ] Tue, 02 January 2007 10:10 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
Check my presentation

http://www.mysqlperformanceblog.com/files/presentations/Euro OSCON2006-High-Performance-FullText-Search.pdf

You're basically dealing with the case which I highlighted as where MySQL Full Text Search performs extremely poor - when you filter by extra clause in additional to full text search.

One of suggestion for you would be to split area into squares, name then with keywords and also use full text search (boolean) to perform search on these.

Ie square with coordinates 15,25 may map to keyword X15Y25 or something similar.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: Map bounding box.. [message #523 is a reply to message #521 ] Tue, 02 January 2007 17:18 Go to previous messageGo to next message
oordopjes is currently offline oordopjes
Messages: 15
Registered: January 2007
Junior Member
Ok.. I understand that the fulltext can be optimized. But Peter, do you have some other suggestions? Should I use one or two tables? And what indexes??
Report message to a moderator
Re: Map bounding box.. [message #524 is a reply to message #523 ] Tue, 02 January 2007 17:28 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
One table will likely be faster. But you better to benchmark it - a lot depends on data cardinality.

You will need field which is called "search" or something similar which contains fields you want to search (ie name) plus keywords such as X12Y45 to help with location based searching.

This will work for smaller sizes for larger you need to run fulltext search in parallel or use external solutions.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: Map bounding box.. [message #528 is a reply to message #524 ] Wed, 03 January 2007 18:20 Go to previous messageGo to next message
oordopjes is currently offline oordopjes
Messages: 15
Registered: January 2007
Junior Member
OK. And what if I create multiple text fields all with fulltext indexes. For example:

- One field 'name' with the name.
- One field 'location' with the coordinates (for example X12Y14)
- One field with yet another string..

Using an AND query on all the matches (so: MATCH blabla ON blabla AND MATCH bla ON bla)..

Will this reduce performance by a lot?
Report message to a moderator
Re: Map bounding box.. [message #530 is a reply to message #528 ] Wed, 03 January 2007 18:40 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
MySQL can only use one full text search index for query so it will not help.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: Map bounding box.. [message #533 is a reply to message #517 ] Thu, 04 January 2007 06:04 Go to previous messageGo to next message
bluesaga is currently offline bluesaga
Messages: 20
Registered: December 2006
Junior Member
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.
Report message to a moderator
Re: Map bounding box.. [message #534 is a reply to message #517 ] Thu, 04 January 2007 06:18 Go to previous messageGo to next message
oordopjes is currently offline oordopjes
Messages: 15
Registered: January 2007
Junior Member
Does Sphinx allow you to use more FULLTEXT indexes? Or is it the same as with MySQL?
Report message to a moderator
Re: Map bounding box.. [message #535 is a reply to message #517 ] Thu, 04 January 2007 06:24 Go to previous messageGo to next message
bluesaga is currently offline bluesaga
Messages: 20
Registered: December 2006
Junior Member
it allows you to use more than one index at a time. However it might not be how your thinking it would Wink
Report message to a moderator
Re: Map bounding box.. [message #537 is a reply to message #535 ] Thu, 04 January 2007 06:58 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
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

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: Map bounding box.. [message #538 is a reply to message #535 ] Thu, 04 January 2007 07:14 Go to previous messageGo to next message
oordopjes is currently offline oordopjes
Messages: 15
Registered: January 2007
Junior Member
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?
Report message to a moderator
Re: Map bounding box.. [message #539 is a reply to message #538 ] Thu, 04 January 2007 07:27 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
You can use multiple matches in MySQL but it only will use FT index effectively if list of columns matches list of columns in the index as index does not store information about in which column keyword appeared.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: Map bounding box.. [message #669 is a reply to message #539 ] Fri, 02 February 2007 05:11 Go to previous messageGo to next message
oordopjes is currently offline oordopjes
Messages: 15
Registered: January 2007
Junior Member
When I have a table with a fulltext index say 'a' and another fulltext index 'b'. What would the performance be of the following query types:
1. select * from x where match 'a' against 'blabla' in boolean mode and match 'a' against 'blabla' (not in boolean mode)..
2. select * from x where match 'a' against 'blabla' and match 'b' against 'blabla'

I understood MySQL can only use ONE fulltext index for the query.. is it used for both 'matches' in the first case?
And for better performance in the second case should I create 2 tables.. so MySQL can use the fulltext index on each table 'a' and 'b' and then JOIN?
Report message to a moderator
Re: Map bounding box.. [message #670 is a reply to message #669 ] Fri, 02 February 2007 06:51 Go to previous message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
No. Yo use single FullText index and than match on something like

(X01 | X03 | X03) and (Y01 | Y02 Y03)

Note index on (X,Y) may also work with efficiently with IN but not with range

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Previous Topic:mysql not using index
Next Topic:can't open .ibd file error
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Sat Jul 4 14:38:23 EDT 2009

Total time taken to generate the page: 0.04072 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.