# Seaching points within a radius - performance issues

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(',
',',
')')),  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
WHERE
MbrContains(GeomFromText (
CONCAT('LINESTRING(',
',',
')')),  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

Question 2: Let's remove the `JOIN...` and change `AND status=1` into
``````AND EXISTS ( SELECT * FROM users
(I assume `uid` is the `PRIMARY KEY` of `users`?)