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