我正在开发一个交通程序，它迫使我创建一个长查询来搜索可能的路线。我需要尽可能优化查询。它计算公交车站之间的中途停留。人们在一个车站下车，然后在半径为公里的范围内在另一个车站上车。我必须确保路径不受某些规则的限制。
如何优化查询？
问题是t2和t3之间的关系以及t5和t6之间的关系，它们通过半径连接。

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