Færdiggjort

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

Evner: Database Administration, Programmering af database, MySQL, PHP, SQL

Se mere: 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

Om arbejdsgiveren:
( 38 bedømmelser ) Sydney, Australia

Projekt ID: #17655277

Tildelt til:

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 Flere

$100 AUD in 2 dage
(1686 bedømmelser)
8.0

35 freelancere byder i gennemsnit $175 på dette job

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 Flere

$155 AUD in 3 dage
(118 bedømmelser)
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: Flere

$155 AUD in 3 dage
(73 bedømmelser)
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 Flere

$220 AUD in 5 dage
(237 bedømmelser)
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 Flere

$150 AUD in 3 dage
(110 bedømmelser)
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 Flere

$250 AUD in 3 dage
(156 bedømmelser)
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 Flere

$200 AUD in 5 dage
(126 bedømmelser)
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 Flere

$100 AUD in 4 dage
(85 bedømmelser)
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 på 1 dag
(83 bedømmelser)
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 Flere

$333 AUD in 3 dage
(148 bedømmelser)
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 på 1 dag
(67 bedømmelser)
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 Flere

$155 AUD in 3 dage
(30 bedømmelser)
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 Flere

$250 AUD in 5 dage
(23 bedømmelser)
4.8
$250 AUD in 3 dage
(21 bedømmelser)
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 Flere

$200 AUD in 2 dage
(20 bedømmelser)
4.7
$50 AUD på 1 dag
(25 bedømmelser)
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 Flere

$250 AUD in 7 dage
(61 bedømmelser)
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 Flere

$155 AUD in 3 dage
(23 bedømmelser)
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. Flere

$111 AUD på 1 dag
(6 bedømmelser)
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 in 3 dage
(49 bedømmelser)
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 Flere

$222 AUD in 10 dage
(3 bedømmelser)
3.5