mysql - SQL IN子句的更有效方法

我有这个sql查询，这需要很长时间才能完成。我怎样才能加快速度？
t_inter_species_interaction有60k行，t_pathway有100k。uniprot_id_1、uniprot_id_2、uniprot_id是varchar类型。
在这个查询中，我想选择uniprot_id_1和uniprot_id_2，它们都存在于t_路径中：

select distinct uniprot_id_1,uniprot_id_2 from t_intra_species_interaction
where uniprot_id_1 in (select uniprot_id from t_pathway) and
  uniprot_id_2 in (select uniprot_id from t_pathway)

在这个例子中，我想选择uniprot_id，它出现在上面第一个查询返回的set uniprot_id中。

select distinct uniprot_id,id from t_pathway as t
where uniprot_id in
(
    select distinct uniprot_id_2 from t_intra_species_interaction
    where uniprot_id_1 in (select uniprot_id from t_pathway) and
      uniprot_id_2 in (select uniprot_id from t_pathway)
    union
    select distinct uniprot_id_1 from t_intra_species_interaction
    where uniprot_id_1 in (select uniprot_id from t_pathway) and
      uniprot_id_2 in (select uniprot_id from t_pathway)
)

谢谢。

最佳答案

子查询是相同的，因此它们可以合并为一个，并移动到一个连接

SELECT DISTINCT i.uniprot_id_1, i.uniprot_id_2
FROM   t_intra_species_interaction i
       INNER JOIN t_pathway p ON p.uniprot_id IN (i.uniprot_id_1, i.uniprot_id_2)

第二个查询
最好是打开一个新的问题来提及这个，但是看看我之前的问题，应该很容易发现，要得到第二个答案，你只需要从túu路径获得列，而不是túu物种间的相互作用

SELECT DISTINCT p.uniprot_id, p.id
FROM   t_intra_species_interaction i
       INNER JOIN t_pathway p ON p.uniprot_id IN (i.uniprot_id_1, i.uniprot_id_2)