•  

Announcement

Announcement Module
Collapse
No announcement yet.

Map bounding box..

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Map bounding box..

    #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!!
    Tags: None

  • #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.

    Comment


    • #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..

      Comment


      • #4
        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?

        Comment


        • #5
          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.

          Comment


          • #6
            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??

            Comment


            • #7
              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.

              Comment


              • #8
                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?

                Comment


                • #9
                  MySQL can only use one full text search index for query so it will not help.

                  Comment


                  • #10
                    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.

                    Comment


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

                      Comment


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

                        Comment


                        • #13
                          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

                          Comment


                          • #14
                            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?

                            Comment


                            • #15
                              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.

                              Comment

                              Working...
                              X