select id from A where id in (select id from B) 怎样提高执行速度？

[chenam]

mysql 语句
select id from A where id in (select id from B)
A,B 表很大时，上万记录，速度会很慢，怎样提高执行速度．．．．

[liujuns]

select *
from A a join B b on a.id =b.id
where b.id =??

在Id上建立索引

[-晴天]

select t1.id from A t1 inner join b t2 on t1.id=t2.id

[等不到来世]

--A,B表分别在id上建立索引

select id from A 
join (select distinct id from B) t
on A.id=t.id

[水族杰纶]

create index index_name on a(id)
create index index_name on b(id)
select id from A 
inner join (select distinct id from B) t
on A.id=t.id

[hyde100]

--id上建立索引
select 
	*
from A 
	inner join B  on A.id=B.id

[csdyyr]

--在id上建立索引
select a.id from a join b on a.id=b.id

[fcuandy]

SELECT a.* FROM ta a
INNER JOIN
	(SELECT DISTINCT id FROM tb) b
	ON a.id=b.id

[chenam]

你们用的是join
和我上面的应该不一样的吧．
A表中id是主键，B表中有id,xyrid ,id 是主键

select id from A where id in (select xyrid from B)

[mustudent]

聽說Exists比in的效率高

[水族杰纶]

[Quote=引用 8 楼 chenam 的回复:]
你们用的是join
和我上面的应该不一样的吧．
A表中id是主键，B表中有id,xyrid ,id 是主键

select id from A where id in (select xyrid from B)
[/Quote]

select id from A where id in (select id from B) 
--> 變成下面怎麼不一樣了?
select id from A 
inner join (select distinct id from B) t
on A.id=t.id

select id from A where id in (select xyrid from B) 
-->
select id from A 
inner join (select distinct xyrid from B) t
on A.id=t.id

[幸运的意外]

给B表加个索引,并且尽量限制子查询的结果集.

[Liyingyue_FFS]

主要还是对A B表的ID加索引

[ChinaJiaBing]

select id from a where exists(select 1 from b where a.id=b.id)

[dobear_0922]

SELECT a.* FROM ta a
INNER JOIN
    (SELECT id FROM tb group by id) b
    ON a.id=b.id