Create a MySQL query (using stored procedures?)

Cancelled Posted Feb 24, 2016 Paid on delivery
Cancelled Paid on delivery

We have a MySQL database of all flight routes and schedules in the world, which is updated weekly. We require a freelancer to:

1) Create the proper indexes (hash indexes?) for maximum efficiency

2) Write an SQL query (perhaps using stored procedures?) that recursively searches for a specific route, given inputs of origin, destination, departure date, minimum connection time, and maximum number of connections.

For example, say I wanted to find a route that took me from Auckland, New Zealand (AKL) to London, United Kingdom (LHR), with a minimum of 2 hours between flights and a maximum of 2 connections.

A. The query would first look at Auckland Airport (AKL), and find every possible destination and flight time available from there.

B. The query would then look at all destinations available from each airport returned from the previous result (such as SYD, MEL, BNE, HKG, etc), that have flights leaving at least 2 hours after the arrival from AKL.

C. The query would then look at all destinations available from *those* airports returned from the previous result that have flights leaving at least 2 hours after the arrival from the previous airports.

D. Because the last airport was teh final connection, this step would look only for flights leaving at least 2 hours after the arrival which have their destination as LHR.

Note that flights with less connections can (and should) be returned also. For example, if there was a direct flight between AKL and LHR, or a flight with one connection (AKL-HKG-LHR).

Some flights do not operate on certain days, so day of the week is also important, and should be deciphered from departure date provided. You also need to confirm the effective_from and effective_to dates to ensure you are using the right schedule for the date.

The query should be able to return a result for 2 connections in less than 10 seconds.

Example data is attached and is a result of running the following query:

select * from schedules WHERE (origin='AKL' OR origin='SYD' OR origin='MEL' OR origin='SIN' OR origin='HKG') AND (destination='AKL' OR destination='SYD' OR destination='MEL' OR destination='SIN' OR destination='HKG' OR destination='LHR') AND codeshare IS NULL

Big Data Sales Database Development Database Programming MySQL Software Architecture

Project ID: #9752863

About the project

12 proposals Remote project Active Feb 25, 2016

Awarded to:

$155 NZD in 3 days
(27 Reviews)
6.2
valentine8

DB observe and data investigation.

$520 NZD in 5 days
(1 Review)
2.5

12 freelancers are bidding on average $199 for this job

phiona06

Hi! I'm senior lecturer on databases and I've more 10 years experience in MySQL. I can create indexes and effective query on your task.

$155 NZD in 3 days
(25 Reviews)
4.7
TechnoVilla

Dear Sir/Madam, I have over 8 years of work experience in PHP, MySQL, Database Development, Database Programming, HTML, CSS, Linux, Javascript, jQuery, .NET, Bootstrap, C, C++, C# and so on... Please visit the More

$250 NZD in 3 days
(9 Reviews)
3.3
ZishanChuhan

I am oracle certified professional and expert in databases and query design. I can do the task as per your requirements.

$49 NZD in 1 day
(15 Reviews)
3.7
khannouman

Hi Hope you are fine I am qualified webdeveloper with experience in C#,Asp.net ,mvc,SqlServer 2008r2.. fully understand your query. I have a logic to implement it which will display result according to your des More

$50 NZD in 1 day
(7 Reviews)
3.0
sitendrasahu9

hello sir plz let me know if u want help from myself. ------------------------------------------------------------------------------------------ Regards Ravi Sharma

$166 NZD in 1 day
(1 Review)
1.5
bennxumalo

the best case who require me seeing the structure of the tables in question even with dummy data in order to assess and write the best way possible. ITS basically more of a mathematically challenge (Permutations) wri More

$155 NZD in 3 days
(0 Reviews)
0.0