Optimise Tinder type mysql query

Question

I am new to mysql but have built the following query which

1. Finds nearby users using a table of latlong POINTs (table pos).
2. Removes users who have already been rated (table swipes).
3. Joins the results to the users table.

E.g. find people near POINT(95,95) for user with id = 1 (latlong values simplified)

SELECT users.id, name, email, gender, birthyear, latlong FROM (
SELECT * FROM (
    # Find nearby users.
    SELECT * FROM pos
        WHERE X(latlong) BETWEEN 90.0 AND 100.0
        AND Y(latlong) BETWEEN 90.0 AND 100.0
) AS nearby WHERE owner NOT IN (
    # Find users already rated.
    SELECT target FROM swipes WHERE owner = 1
) AND id != 1
) AS unratedNearby JOIN users ON unratedNearby.owner = users.id;

This all works perfectly but I am concerned about the complexity of this query and how it will scale. I have a SPATIAL KEY 'latlong' ('latlong') on table pos (I realise this is a suboptimal way of finding nearby users but accuracy isn't that important here). Any one user can have an infinite amount of swipes.

Will this query begin to break down once the users and swipes tables start to become very large? Are there any indexes I should be using besides the spatial key?

Answer 1

Your query seems like too complicated for this simple task. Also your method of finding nearby users seems really inaccurate for finding people task.Consider this query with haversine formula as distance functions (examples of this functions can be easily found online)

SELECT user_id,name,email,gender,birthyear,latlong,distance(latlong) as      
distance
FROM pos p left join swipes s on p.user_id = s.owner
WHERE target_id is NULL
ORDER by distance asc

This query can be wrong because you haven't provided your create table statements. But logic is right. You join table of position of users on table of swipes and take rows with no records, than you order your results by distance to get nearby people.