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 DISTINCTline.id                             AS line_id,line.type                           AS line_type,TIME_TO_SEC(stop.timeDeparture)     AS time_departure,stop.id_station                     AS departure_idFROM        `stops`     AS stopINNER 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.timeArrivalThis 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?
Query for searching for the best transportation
php;mysql;database;search