mysql - 如何优化包含相同“in”语句的sql？

Question

select *
from t_link a
WHERE a.STAR_CITY_ID in (select cityId from `t_map_city` t
                         WHERE t.PROVINCEID = 350) and
      a.END_CITY_ID in (select cityId from `t_map_city` t
                        WHERE t.PROVINCEID = 350);

这是我的sql。如何优化它以提高效率？

select * from `t_ne$link` a 
JOIN (select cityId from `t_map_city` t WHERE t.PROVINCEID=350) tb1 
  on (a.END_CITY_ID in (tb1.cityId) and a.STAR_CITY_ID in (tb1.cityId))； 

我已将其更改为此，但它不起作用。

标签： mysqlsql

Answer 1

好吧，使用EXISTS也可能会有所帮助：

select *
from t_link a
WHERE exists(select 1 from `t_map_city`
             where cityId = a.start_city_id and provinceId = 350)
and exists(select 1 from `t_map_city`
             where cityId = a.end_city_id and provinceId = 350)