I made a website where is possible to search for the best transportation from a place to another. For instance, if I need to go from Berlin to Bremen, the website will search in its internal database for all the trains and buses that will cover that distance.
To give the results, the websites look for all the trains which have Berlin or Bremen in their stops and will also match trains which have only one of the stations as stop.
For example, I look for Berlin-Bremen, the website will also find a solution which includes a train that cover the track Milan-Dresden and stops also in Berlin, after 20 minutes a bus leave from Berlin and goes to Paris but stops in Bremen.
As you can imagine the query is quite complicated and it might take a lot of time to find the results.
This is the main query I've done:
SELECT DISTINCT line.id AS line_id, line.type AS line_type, TIME_TO_SEC(stop.timeDeparture) AS time_departure, stop.id_station AS departure_id FROM `stops` AS stop INNER JOIN `days` AS day ON ( day.id_line = stop.id_line ) INNER JOIN `lines` AS line ON ( line.id = stop.id_line ) INNER JOIN `stations` AS station ON ( station.id = stop.id_station ) WHERE ( line.status = 1 AND day.day = ' . $this->day_of_the_week . ' AND line.type IN (' . $this->type_of_transport . ') AND stop.id_station IN (' . $stations_in_departure_location . ') AND time_departure > ' . $min_time_plus_walk_time . ' ) OR ( line.status = 1 AND day.day = ' . $day_of_the_week_tomorrow . ' AND line.type IN (' . $this->type_of_transport . ') AND stop.id_station IN (' . $stations_in_departure_location . ') AND time_departure > ' . $night_min_time_plus_walk_time . ' AND time_departure < ' . $night_max_time . ' ) ORDER BY stop.timeDeparture, stop.timeArrival
This is the database structure:
I made a recursive search function that uses this same query to find every train/bus that you could take from any of the cities where any of the trains/buses found in the iteration before stops.
Every time it finds a combination of lines that reach the chosen destination, it adds it to an array which will be then sorted and filtered depending on the users preferences.
I had to limit the iteration count to 2, otherwise it gets too slow.
Can you please suggest best practices, a better solution or a way to improve it?