I'm looking for an expert MySQL query writer who can optimise my query so that it runs "near-instantly". Here is my problem:
- I have a table called "Map" that stores the lat/lng pairs of certain global co-ordinates.
The Map table currently holds about 4,000 rows, but I expect this will grow to many millions in future.
- I also have a User table that stores the ID of my users. The User table currently holds a few hundred rows, but may grow to tens of thousands (however the scale of this table is not that important for the purposes of this project).
- Many rows in the Map table also assign co-ordinates to Users, so we can consider that certain locations in the Map table "belong" to certain Users.
- What I want to find is for every Map row which belongs to a given user, how many Map rows that do not belong to this User are within a certain radius of each Map row.
- So for example, if User "a" has 400 Map rows which belong to him, and I set the radius to 500 metres, I want to find every Map row in the Map table which DOES NOT belong to him, which is within the radius of every single Map row which DOES belong to him.
This is my current query, which works successfully and is 100% accurate:
SELECT Map.map_ID, [login to view URL], [login to view URL], Map.user_ID, [login to view URL],
(6378137 * acos(cos(radians($lat)) * cos(radians(lat)) * cos(radians(lng) - radians($lng)) + sin(radians($lat)) * sin(radians(lat))))
AS distance FROM Map
LEFT JOIN Users ON Map.user_ID = Users.user_ID
WHERE Map.user_ID != 0 AND Map.user_ID != $userID
HAVING distance < $distance;
This individual query is basically instant on my MySQL server. The problem is that when I execute this query from PHP (using mysqli_multi_query), running the query for a user who owns 400 rows on the Map table takes 30-40 seconds to return a result. I need a near-instant result.
You need to bear in mind that the query will be executed via PHP, so the solution you propose must work coming from a script (i.e. it can't work just by accessing the database directly using phpMyAdmin or equivalent). The variables $lat, $lng, $userID and $distance all come from the PHP script.
Just to be 100% clear: the above query works perfectly. What I am looking for is someone to OPTIMISE my query so that it runs as fast as possible. The time I am targeting is less than 1 second for a User with 10,000 rows belonging to him, having to scan a table with 10 million rows. Your benchmark for success will be for your solution to return a result (excluding network overhead) on my server of a user with 400 rows belonging to him, having to scan a table with 4,000 rows in less than 10 milliseconds.
I can supply database schemas and sample data from both tables if required.
I look forward to working with you!
35 freelanceria on tarjonnut keskimäärin %project_bid_stats_avg_sub_26% %project_currencyDetails_sign_sub_27% tähän työhön
I can optimize your SQL query of Map Co-ordinates data, Please contact to discuss and proceed further, thanks. Relevant Skills and Experience MySQL