同样的语句，查询效率相差万倍的SQL，为什么呢，我该如何处理

Question

今天发现一个SQL执行的特别慢，就在mysql客户端上运行了一些相关的sql语句，结果如下

1：/*[20:11:25][ 187 ms]*/

select count(*) from MD_VENDOR_MATERIAL ;

  结果为：353329条记录，

 

2：/*[20:10:51][  15 ms]*/

select distinct a.org_id from SYS_USER a, REMIND_PERSONAL_SETTING b where a.USER_ID = b.USER_ID and b.ITEM_ID = -79 and REMIND_VALUE = 1 ;

 结果为：868,4974,5006,754,147,42,727,349,5368,117,5001

 

3：/*[20:29:10][  15 ms]*/

select count(*) from MD_VENDOR_MATERIAL where VENDOR_ID in (868,4974,5006,754,147,42,727,349,5368,117,5001) ;

 结果为：17048条记录

 

4：/*[20:07:10][250812 ms]*/

select count(*) from MD_VENDOR_MATERIAL where VENDOR_ID in (select distinct a.org_id from SYS_USER a, REMIND_PERSONAL_SETTING b where a.USER_ID = b.USER_ID and b.ITEM_ID = -79 and REMIND_VALUE = 1) ;

 结果为：17048条记录

 

3和4的区别就是in中一个是查询后的值，另一个直接是子查询

两者的效率竟然相差了万倍以上，差异

请大家帮忙看看，因为程序中还有很多in中直接是放入sql语句的，要不要更改。这到底是怎么回事

 

mysql  Ver 14.12 Distrib 5.0.72, for pc-solaris2.10 (x86_64) using readline 5.1

 

 

Answer 1

用in(select ...)方式，会导致查询计划只能由主表开始，即先全表扫描MD_VENDOR_MATERIAL再挨个检查是否符合where条件，所以很慢。
有两种方法：
1.用两台SQL查询，即先查出 你的SQL 2 的结果，再把它当常数传给SQL 1
2.改写SQL为
select count(*) from MD_VENDOR_MATERIAL m, SYS_USER a, REMIND_PERSONAL_SETTING b where m.VENDOR_ID = a.org_id and a.USER_ID = b.USER_ID and b.ITEM_ID = -79 and REMIND_VALUE = 1 ;

这样MySQL能自动选择更优的查询计划，而不是象之前一样必须从主表MD_VENDOR_MATERIAL开始扫描。