Question

table1
row_id      row_one     row_two
1           1           5
2           1           5
3           2           5
4           2           5
5           2           6

table2
row2_id     row2_one    row2_two
1           1           somevalue
2           2           somevalue2

"select distinct row_one from table1 where row_two=5"

结果结果结果结果结果

row_one
1
2

之后,我想要选择

select * from table2 where row2_one=1
select * from table2 where row2_one=2

i want select with one query. i am trying this query

 select * from table2 where row2_one in (select distinct row_one from table1 where  
          row_two where row_two=5)

but it took 8s Showing rows 0 - 14 ( 15 total, Query took 8.3255 sec)

why is it so slow. i want select faster. please help me!

Answer 1

您不需要里面有 DISTINCT 。您可以做 :

SELECT * 
FROM table2 
WHERE row2_one IN (SELECT row_one FROM table1 WHERE row_two=5)

使用 EXISTS 可能更快:

SELECT * 
FROM table2 A
WHERE EXISTS  (SELECT * FROM table1 WHERE row_two=5 AND row_one = A.row2_one)

Answer 2

让我补充一下所谓的bellow - 使用 < a href=" http://readvitamin.com/2007/10/18/using-% E2%80%9Cgroup-by% E2%80%9D-smode-oste-%E2%8080%9Clear%E2%80%9D-clause/"rel=nofollow" >GROUP by 或 < a href=" "http://www.zimbio.com/member/vijaya_krishna_birju/articles/M0M0UJrtZab/use+EXISTS+incested+DISTT+improveuery" rel=“nofolpolT>EXISTS 而不是DIST, 它可以真正改善你的性能。

Answer 3

假设这是您的查询:

select *
from table2 where row2_one in (select distinct row_one from table1 where row_two=5)

那么这是好的,有一件事,你不需要在小屋里有区别

如果您在列行_ 2 的表1中添加索引, 您应该取得更好的性能。在表2 行2 1 上的索引也会加快速度。

Answer 4

Select distinct table2.* 
from table1 t1, table2 t2 
where t1.row_two =5 and t1.row2_one = t2.row2_one

友情链接