Suoritettu

Optimise my MySQL query

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

Taidot: Tietokannan hallinto, Tietokantaohjelmointi, MySQL, PHP, SQL

Näytä lisää: edit csv mysql query, asp mysql query, 2013 lost connection mysql query, mysql query optimization tips, optimize mysql query online, query optimization in mysql with example, mysql query optimization techniques, mysql query optimization and performance tuning, mysql query optimization tool, mysql explain query, mysql query optimization tips and tricks, php, mysql, sql, database administration, database development, postgresql, mysql query parent child, mysql query report, php mysql query table

Tietoa työnantajasta:
( 38 arvostelua ) Sydney, Australia

Projektin tunnus: #17655277

Myönnetty käyttäjälle:

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 Lisää

$100 AUD 2 päivässä
(1686 Arvostelua)
8.0

35 freelanceria on tarjonnut keskimäärin %project_bid_stats_avg_sub_26% %project_currencyDetails_sign_sub_27% tähän työhön

vishalavi29

Hi there, I am DB specialist and having 4+ years of experience, I can help you Optimise your MySQL queries. I have read your complete project description and I will optimize your query to fetch instant result. I Lisää

$155 AUD 3 päivässä
(118 arvostelua)
8.5
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: Lisää

$155 AUD 3 päivässä
(73 arvostelua)
7.5
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 Lisää

$220 AUD 5 päivässä
(237 arvostelua)
7.4
junkyfunky

Hello, I've read your project description and I want to tell you that I can fix your query of MySQL Database as per your requirments. Lets have a quick chat to discuss this project. We have extensive experience i Lisää

$150 AUD 3 päivässä
(110 arvostelua)
6.6
truongnguyen86

Hello there, i'm expert on Mysql especially with tuning, i've tuned some project with runing from a couple of hours to 10 secs. I've worked with database with millions record so i think no problem. With your query i ca Lisää

$250 AUD 3 päivässä
(156 arvostelua)
6.6
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 Lisää

$200 AUD 5 päivässä
(126 arvostelua)
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 Lisää

$100 AUD 4 päivässä
(85 arvostelua)
5.8
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 1 päivässä
(83 arvostelua)
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 Lisää

$333 AUD 3 päivässä
(148 arvostelua)
5.9
vinaysinghh

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

$111 AUD 1 päivässä
(67 arvostelua)
5.5
CrazyWebGuru

Hello Sir. How are you? Thanks for your posting. I have read your post and very interested in it I am experienced web developer with rich skill. I have enough of experience with mysql, mysqli, database management Lisää

$155 AUD 3 päivässä
(30 arvostelua)
5.2
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 Lisää

$250 AUD 5 päivässä
(23 arvostelua)
4.8
$250 AUD 3 päivässä
(21 arvostelua)
4.8
sashaE319WebGuru

Hello I just read your post and very interested in this job. I think there are two solutions to resolve this problem. 1. You used JOIN for now, but JOIN is so slowly for large tables. So, I am going to remove JOI Lisää

$200 AUD 2 päivässä
(20 arvostelua)
4.7
$50 AUD 1 päivässä
(25 arvostelua)
4.3
mayankinnovative

Dear Client, We are expertise in php, core php, CMS, HTML5, CSS3, bootstrap, WordPress, OpenCart, Magento, PSD to HTML, Angular JS, Node JS photoshop, OPencart, Codeignitor, Laravel and many other technologies. We have Lisää

$250 AUD 7 päivässä
(61 arvostelua)
4.4
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 Lisää

$155 AUD 3 päivässä
(23 arvostelua)
4.4
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. Lisää

$111 AUD 1 päivässä
(6 arvostelua)
4.3
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 3 päivässä
(49 arvostelua)
5.2
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 Lisää

$222 AUD 10 päivässä
(3 arvostelua)
3.5