Optimise my MySQL query

Dokončeno Zveřejněno před 5 lety K zaplacení v momentě doručení
Dokončeno K zaplacení v momentě doručení

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

Správa databází Programování databází MySQL PHP SQL

Identifikační číslo projektu: #17655277

O projektu

29 nabídek Projekt na dálku Aktivní před 5 lety

Uděleno uživateli:

TakeReal

Hello, My name is Sergey. I am Freelancer from [login to view URL], Russia. I can help you as SQL expert to optimize your query. Possible way: you supply me test data, I perform my local work. When I am sure about resu Další

$100 AUD za 2 dní
(1947 recenzí)
8.3

29 Freelnceři na tento projekt zveřejňují nabídky v průměru $176

extreamcode

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: Další

$155 AUD za 3 dní
(93 Recenzí)
7.9
mascotindia123

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 Další

$220 AUD za 5 dní
(280 Recenzí)
7.7
CodingExpertz

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 Další

$200 AUD za 5 dní
(128 Recenzí)
6.6
softcarve

Hi, I have good proficiency in PHP and mysql. I can optimize the query. but, not sure till how much it will be optimized. Depends on the real time situation. you can also share the access to PHP file. so that i can Další

$100 AUD za 4 dní
(128 Recenzí)
6.3
webprog4hire

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

$250 AUD za 1 den
(83 Recenzí)
5.9
mitkoradev

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 Další

$155 AUD za 3 dní
(50 Recenzí)
5.9
SonITvWorker

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 Další

$333 AUD za 3 dní
(153 Recenzí)
5.9
paulprabhakarand

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 Další

$250 AUD za 5 dní
(36 Recenzí)
5.5
vinaysinghh

I am MySQL DBA having more than 8 yrs of experience and I have done many projects on MySQL performance tunning

$111 AUD za 1 den
(69 Recenzí)
5.7
intellivinay

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. Další

$111 AUD za 1 den
(11 Recenzí)
4.8
jagankolkata

I can optimize your SQL query of Map Co-ordinates data, Please contact to discuss and proceed further, thanks. Relevant Skills and Experience MySQL

$155 AUD za 3 dní
(56 Recenzí)
5.5
balexus

I'd like interesting tasks. Probably the task have a ready decision. I want to solve your problem. Your current SQL code looks almost all records in the "Map" table. It is not good. It is need to change structure of "M Další

$222 AUD za 10 dní
(4 Recenzí)
3.6
namankhinda3

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 Další

$188 AUD za 3 dní
(3 Recenzí)
1.2
robbinpeng

I have 8 year's experience in MySQL, could fulfill your requirement. We can discuss about the details, and the deadline of project.

$144 AUD za 10 dní
(1 recenze)
0.8
prismsoftit

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 Další

$155 AUD za 10 dní
(0 Recenzí)
0.0
Amil1975

Extensive experience in software industry . Deliver the solution on time. Reliable

$155 AUD za 3 dní
(0 Recenzí)
0.0
abramoth

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.

$88 AUD za 1 den
(0 Recenzí)
0.0
kallraju

Expert in MySQL queries and optimization. having 18+ years experience in database optimization. Handled 300,00,00,000 transactions in a month

$222 AUD za 3 dní
(0 Recenzí)
0.0
YameenButt78670

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 Další

$222 AUD za 3 dní
(0 Recenzí)
0.0