skip to Main Content

I’m working on a transport program that forces me to create a long query to search for possible routes. I need to optimize the query as much as possible. It calculates stopovers between bus stops. People get off at one stop and get on at another within a radius in kilometers. I have to ensure that the path is not prevented by some rules.

How can I optimize the query?

The problem is the relationship between t2 and t3 and the relationship between t5 and t6 which are joined by a radius.

SELECT '3' AS type, s1.id_sott AS id_sott1,s2.id_sott AS id_sott2,s3.id_sott AS id_sott3,s4.id_sott AS id_sott4, s5.id_sott AS id_sott5,s6.id_sott AS id_sott6, '0' AS id_sott7, '0' AS id_sott8, ch1.changeid as changeid1, ch2.changeid as changeid2, '0' AS changeid3,
    ABS((s2.distance - s1.distance)) as dist1, ABS((s4.distance - s3.distance)) as dist2, ABS((s6.distance - s5.distance)) as dist3,'0' AS dist4, (ABS((s2.distance - s1.distance)) + ABS((s4.distance - s3.distance)) + ABS((s6.distance - s5.distance)) ) AS km,
    s1.id_corsa AS id_corsa1,s3.id_corsa AS id_corsa2,s5.id_corsa AS id_corsa3,'0' AS id_corsa4, s1.orario AS orariostart1,s2.orario AS orariostop1, s3.orario AS orariostart2, s4.orario AS orariostop2,s5.orario AS orariostart3, s6.orario AS orariostop3,'0' AS orariostart4,
    IFNULL(
        SELECT GROUP_CONCAT(corse)
        FROM regole_linee
        WHERE ('2023-02-24' BETWEEN da AND a )
        AND FIND_IN_SET( (DAYOFWEEK( '2023-02-24' ) -1 ) , giorni_sett)
        AND id_az= 28 AND stato=1
        , '0'
    ) AS rl,
    111.111 * DEGREES(ACOS(LEAST(1.0, COS(RADIANS(t3.lat)) * COS(RADIANS(t2.lat)) * COS(RADIANS(t3.lon - t2.lon)) + SIN(RADIANS(t3.lat)) * SIN(RADIANS(t2.lat))))) AS dist_frompart1,
    111.111 * DEGREES(ACOS(LEAST(1.0, COS(RADIANS(t5.lat)) * COS(RADIANS(t4.lat)) * COS(RADIANS(t5.lon - t4.lon)) + SIN(RADIANS(t5.lat)) * SIN(RADIANS(t4.lat))))) AS dist_frompart2,
    '0' AS dist_frompart3

FROM corse_fermate AS s1
INNER JOIN corse_fermate AS s2 ON s1.id_corsa = s2.id_corsa 
INNER JOIN corse_fermate AS s3
INNER JOIN corse_fermate AS s4 ON s3.id_corsa = s4.id_corsa 
INNER JOIN corse_fermate AS s5
INNER JOIN corse_fermate AS s6 ON s5.id_corsa = s6.id_corsa 
INNER JOIN tratte_sottoc AS t ON t.id_sott=s1.id_sott
INNER JOIN tratte_sottoc AS t2 ON t2.id_sott=s2.id_sott 
INNER JOIN tratte_sottoc AS t3 ON t3.id_sott=s3.id_sott
INNER JOIN tratte_sottoc AS t4 ON t4.id_sott=s4.id_sott 
INNER JOIN tratte_sottoc AS t5 ON t5.id_sott=s5.id_sott
INNER JOIN tratte_sottoc AS t6 ON t6.id_sott=s6.id_sott 
/*
INNER JOIN tratte_sottoc_tratte AS tt1 ON (s1.id_sott=tt1.id_sott1 AND s2.id_sott=tt1.id_sott2) 
INNER JOIN tratte_sottoc_tratte AS tt2 ON (s3.id_sott=tt2.id_sott1 AND s4.id_sott=tt2.id_sott2)
INNER JOIN tratte_sottoc_tratte AS tt3 ON (s5.id_sott=tt3.id_sott1 AND s6.id_sott=tt3.id_sott2) 
*/

INNER JOIN changeover AS ch1 ON s2.id_sott=ch1.changeid
INNER JOIN changeover AS ch2 ON s4.id_sott=ch2.changeid 

WHERE s1.id_sott = 3
AND s6.id_sott = 85
AND s2.ordine > s1.ordine AND s4.ordine > s3.ordine AND s6.ordine > s5.ordine
AND s1.id_corsa != s3.id_corsa AND s1.id_corsa != s5.id_corsa AND s3.id_corsa != s5.id_corsa
AND s1.id_sott != s2.id_sott AND s6.id_sott != s4.id_sott AND s2.id_sott != s4.id_sott
AND s1.stato=1 AND s3.stato=1  AND s5.stato=1 
AND TIMESTAMPDIFF(MINUTE, s2.orario, s3.orario) >= 0
AND TIMESTAMPDIFF(MINUTE, s2.orario, s3.orario) <= 180
AND TIMESTAMPDIFF(MINUTE, s4.orario, s5.orario) >= 0
AND TIMESTAMPDIFF(MINUTE, s4.orario, s5.orario) <= 180 


/*AND s1.id_az=1 AND s2.id_az=1 AND s3.id_az=1 AND s4.id_az=1 AND s5.id_az=1 AND s6.id_az=1 AND ch1.id_az=1 AND ch2.id_az=1 */

GROUP BY s1.id_sott,s2.id_sott,s3.id_sott,s4.id_sott,s5.id_sott,s6.id_sott,s1.id_corsa,s3.id_corsa,s5.id_corsa 

HAVING dist_frompart1 < 5
AND dist_frompart2 < 5
AND find_in_set(s1.id_corsa,rl) = 0  
AND find_in_set(s3.id_corsa,rl) = 0 
AND find_in_set(s5.id_corsa,rl) = 0 

ORDER BY km ASC LIMIT 5

2

Answers


  1. Instead of using a subquery, you can do a left join with the regole_linee table, you will have the same result with less effort.

    Login or Signup to reply.
  2. These indexes may help performance:

    corse_fermate:  INDEX(stato, id_sott, ordine, id_corsa, distance, orario)
    tratte_sottoc:  INDEX(id_sott,  lat, long)
    

    Does regole_linee have an index starting with id_az?

    DATE_FORMAT( '2023-02-24 00:00:00', '%Y-%m-%d %H:%i:%s' ) can be simplified to simply '2023-02-24'.

    D

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search