MySQL Performance: MySQL => Map bounding box..

Members

Help

Help

Register

Login

Home

Home » Performance » MySQL » Map bounding box..

Show: Today's Messages :: Show Polls :: Message Navigator

Switch to threaded view of this topic

Create a new topic

Submit Reply

Map bounding box.. [message #517]

Mon, 01 January 2007 13:50

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

Send a private message to this user

Re: Map bounding box.. [message #518 is a reply to message #517 ]

Mon, 01 January 2007 14:47

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

Send a private message to this user

Re: Map bounding box.. [message #519 is a reply to message #518 ]

Mon, 01 January 2007 18:10

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

Send a private message to this user

Re: Map bounding box.. [message #520 is a reply to message #519 ]

Mon, 01 January 2007 18:20

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

Send a private message to this user

Re: Map bounding box.. [message #521 is a reply to message #520 ]

Tue, 02 January 2007 10:10

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

Send a private message to this user

Re: Map bounding box.. [message #523 is a reply to message #521 ]

Tue, 02 January 2007 17:18

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

Send a private message to this user

Re: Map bounding box.. [message #524 is a reply to message #523 ]

Tue, 02 January 2007 17:28

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

Send a private message to this user

Re: Map bounding box.. [message #528 is a reply to message #524 ]

Wed, 03 January 2007 18:20

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

Send a private message to this user

Re: Map bounding box.. [message #530 is a reply to message #528 ]

Wed, 03 January 2007 18:40

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

Send a private message to this user

Re: Map bounding box.. [message #533 is a reply to message #517 ]

Thu, 04 January 2007 06:04

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

Send a private message to this user

Re: Map bounding box.. [message #534 is a reply to message #517 ]

Thu, 04 January 2007 06:18

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

Send a private message to this user

Re: Map bounding box.. [message #535 is a reply to message #517 ]

Thu, 04 January 2007 06:24

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

Wink

Report message to a moderator

Send a private message to this user

Re: Map bounding box.. [message #537 is a reply to message #535 ]

Thu, 04 January 2007 06:58

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

Send a private message to this user

Re: Map bounding box.. [message #538 is a reply to message #535 ]

Thu, 04 January 2007 07:14

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

Send a private message to this user

Re: Map bounding box.. [message #539 is a reply to message #538 ]

Thu, 04 January 2007 07:27

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

Send a private message to this user

Re: Map bounding box.. [message #669 is a reply to message #539 ]

Fri, 02 February 2007 05:11

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

Send a private message to this user

Re: Map bounding box.. [message #670 is a reply to message #669 ]

Fri, 02 February 2007 06:51

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

Send a private message to this user

Switch to threaded view of this topic

Create a new topic

Submit Reply

Previous Topic:	mysql not using index
Next Topic:	can't open .ibd file error

Goto Forum:

-=] Back to Top [=-

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

]

Current Time: Wed Jan 7 13:51:07 EST 2009

Total time taken to generate the page: 0.02053 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.

Powered by: FUDforum 2.7.5.
Copyright ©2001-2006 FUD Forum Bulletin Board Software