Optimise my MySQL query
$30-250 AUD
Оплачується при отриманні
Hi there!
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!
Arj
ID Проекту: #17655277
Про проект
29 фрілансерів(-и) готові виконати цю роботу у середньому за $176
HI There, I can assist you Optimize MySQL query. I am very passionate about quality of my work, You will be pleased working with me. Let's have a quick chat to discuss it further. You can check my sample work at: Більше
Hello there, Myself having has 10 + years experience of web site development. Expert in Custom programming in php / Mysql / Jquery / Wordpress/ Joomla / OSC / Magento /OpenCart / Android / Mobile development / Resp Більше
Greetings, I used the same type of concept here and using very optimized queries...please have a look. I am an Independent Professional with 12+ years (https://www.freelancer.com/u/CodingExpertz.html) . Please gi Більше
hello i am expert in sql and ready to solved this prob in 1 day time frame kindly check my freelancer profile n lets discuss here
Hi, may you send me a testdb In my experience I have found that adding a n index on long, and adding a lon<lonx+r and lon >lonx-r befoore the custom diatance calculation may help much in big tables Relevant Skills a Більше
Hi, I worked with Php & Mysql over 15 years. I did lot of project using big data, optimized lot of queries & web sites too. To resolve your problem, we must analysis your query first, to see what indexes used. Then we Більше
Hi. This is Paul Devanesan has done Master's of Science in Computers with more than 1 decade designing, developing and deploying applications on Desktop and Web based for various technology stacks. I am a certified Ja Більше
I am MySQL DBA having more than 8 yrs of experience and I have done many projects on MySQL performance tunning
Hi, I have read all your description about MAP table in SQL and got all mind set how to fix your problem. Well, I have 7+ years of experience in PHP, HTML,SQL, Database Administration, Database Programming, MySQL. Більше
I can optimize your SQL query of Map Co-ordinates data, Please contact to discuss and proceed further, thanks. Relevant Skills and Experience MySQL
Hello, How can I check that query? I did not use functions like sin,cos,radius etc. but i think through php these function are taking time to conversion of longtitude and latitude. and exact after that there is lef Більше
I have 8 year's experience in MySQL, could fulfill your requirement. We can discuss about the details, and the deadline of project.
We thePRISMSOFT IT TECHNOLOGIES primary goal is to create websites using the latest technologies and trends designed to portray the CLIENT’S aims and objectives, We hold one goal above all others: 100% client satisfac Більше
Hello Arj, I have strong background in MySQL. I can help you and I'm ready to work on it until the problem will be solved. Need to see your DB schema. Looking forward tohear from you.
Expert in MySQL queries and optimization. having 18+ years experience in database optimization. Handled 300,00,00,000 transactions in a month
Hi, Welcome to GD, here I am providing C# Desktop Applications with SQL Server Integration. I have a vast experience in Development and designing. I have gone though your projects requirements and details and I Більше