Seaching points within a radius - performance issues

Question

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

Answer 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.