2

i'm in trouble and completely lost with a geometry search, using 2 tables, linked by uid, each one contains about 5 millions rows. One table with address and point coordinates, Second with user status and some other infos as username.

Deal is to find all users having coordinates within a radius of N kilometers. Central point is my proper coordinates.

First question (infos. haversine is a simple mysql function, working well; coordinates is a POINT column with a SPATIAL index)

SELECT uid, city, ROUND(111.045 * haversine(X(coordinates),
       Y(coordinates),40.7484405,-73.9856),2) as userDistance
    FROM userprofile 
    JOIN ( SELECT 40.7484405 AS latpoint, -73.9856 AS longpoint,
                  10 AS radius, 111.045 as units
         ) AS g ON 1=1
    WHERE 
        MbrContains(GeomFromText (
        CONCAT('LINESTRING(',
              latpoint-(radius/units),' ',
              longpoint-(radius /(units* COS(RADIANS(latpoint)))),
              ',', 
              latpoint+(radius/units) ,' ',
              longpoint+(radius /(units * COS(RADIANS(latpoint)))),
              ')')),  coordinates); 

This works fine, less than 0.015 to return about 5000 rows, and EXPLAIN tells me that SPATIAL index is used. But I query only the first table.

When adding an ORDER BY userDistance LIMIT 50 for example, works fine too, except the use of EXPLAIN tells me : "Using temporary; Using filesort"; So First question : is the using temporary a problem and how to avoid it ?

SECOND problem is more complex and more serious. If I query both tables with an INNER JOIN and add a WHERE clause like table2.status = 1, query will scan all rows, ignore the SPATIAL index, and take about 10 minutes.

SELECT uid, city, ROUND(111.045 * haversine(X(coordinates),
       Y(coordinates),40.7484405,-73.9856),2) as userDistance
    FROM userprofile
    JOIN ( SELECT 40.7484405 AS latpoint, -73.9856 AS longpoint,
                  10 AS radius, 111.045 as units
         ) AS g ON 1=1
    INNER JOIN users on userprofile.uid = users.uid
    WHERE  
        MbrContains(GeomFromText (
        CONCAT('LINESTRING(',
          latpoint-(radius/units),' ',
          longpoint-(radius /(units* COS(RADIANS(latpoint)))),
          ',', 
          latpoint+(radius/units) ,' ',
          longpoint+(radius /(units * COS(RADIANS(latpoint)))),
          ')')),  coordinates) AND user_status = 1; 

Putting the users table in a sub query provided the same results : 10 minutes.

I try multiple solutions, but still lost. thanks

  • Putting the user_status column in the table 1, with coordinates, seems to resolve the SECOND problem. But i don't understand why, and this soluton is not very convenient with my database model – Zoé R. Nov 13 '16 at 23:29
  • You should really upgrade to MySQL 8 if you're doing GIS. This would be much faster and easier. – Evan Carroll Jan 13 '19 at 22:31
1

Question 1: The temp table and filesort are unavoidable; don't worry about it.

Question 2: Let's remove the JOIN... and change AND status=1 into

AND EXISTS ( SELECT * FROM users
                WHERE uid = userprofile.uid
                  AND status = 1 )

(I assume uid is the PRIMARY KEY of users?)

In theory this will delay force the test for status until after the Spatial test.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.