这条子查询如何改成联合查询？

Question

SELECT *
FROM `tbl` 
WHERE  rid = 20 and id > 3 and name='xiaozhao' AND id 
IN ( SELECT  id FROM  `tbl` WHERE  rid = 32 and id > 3 and name='xiaozhao')  
ORDER BY  id DESC  LIMIT 0 , 9

Answer 1

select a.*
from tbl  a inner join tbl b using(id)
where  a.rid = 20 and a.id > 3 and a.name='xiaozhao' 
and b.rid = 32 and b.id > 3 and b.name='xiaozhao'

Answer 2

虽然改成JOIN查询，但仍要看你的索引才能知道是否有优化。

Answer 3

狼兄，我用你的方法去查询我模拟的数据，有重复行，需要用distinct去除，不知道是我数据的毛病还是别的什么

Answer 4

版主真是了解我，我正是为索引问题而来的！首先，我在rid, id上建立了索引create index ind_rid_id on tbl(rid, id);这样建立索引是为了筛选出rid = 20后以id排序！(对于子查询中的子句也可以讲explain中的ref项变为const). 如此，则很适合以下这条查询语句：SELECT *
FROM `tbl` force index(ind_rid_id)
WHERE  rid = 20 and id > 3 and name='xiaozhao'   
ORDER BY  id DESC  LIMIT 0 , 9
但当为了实现'并且'的逻辑时，则需要一个子查询，正如顶楼给的语句:SELECT *
FROM `tbl` force index(ind_rid_id)
WHERE  rid = 20 and id > 3 and name='xiaozhao' AND id 
IN ( SELECT  id FROM  `tbl` force index(ind_rid_id) WHERE  rid = 32 and id > 3 and name='xiaozhao')  
ORDER BY  id DESC  LIMIT 0 , 9
但是问题出来了，对于子查询mysql 优化器没法判断哪个结果集小，哪个结果集大，从而无法实现小结果集驱动大结果集的原则！比如，在tbl表中，符合rid = 20 and id > 3 and name='xiaozhao'的记录为100000条，而符合rid = 32 and id > 3 and name='xiaozhao'的记录为10条，这就造成需要遍历100000*1条记录才能得到最后的结果，但用联合查询join的时候，mysql好像是可以正确计算出哪个结果集小哪个结果集大，进而给出正确的执行计划！不知是不是这样？版主还有什么样的建议？

Answer 5

rid = 20 and id > 3 and name='xiaozhao'这个需要一个索引 (rid, name,id)

Answer 6

select a.* from `tbl` a,`tbl` b where a.id=b.id 
    and  a.rid = 20 and a.id > 3 and a.name='xiaozhao' 
    and b.rid = 32 and b.id > 3 and b.name='xiaozhao'
    ORDER BY  a.id DESC  LIMIT 0 , 9不知道这样行不行