mySql in 语句查询优化

有这么一条sql

UPDATE  product set BuyerCount =BuyerCount+1 where ProductId in(  SELECT ProductId from orderproductdetail where OrderId=2014042514163700856 );

　　在数据为几欠条的情况下耗时为20多秒。把这条语句拆分之后

 

SELECT ProductId from orderproductdetail where OrderId=2014042514163700856 

　　耗时0.01秒 结果为：2012

 

UPDATE  product set BuyerCount =BuyerCount+1 where ProductId in(2012)

　　耗时0.02秒。这就奇怪了，两个时间都很短，为啥放一起就那么长时间。

 

 

下面总结了三种优化方法：

 

UPDATE  product p, orderproductdetail o set  p.BuyerCount =p.BuyerCount+1    where   p.ProductId=o.ProductId and  o.OrderId=2014042514163700856;

UPDATE product p INNER JOIN orderproductdetail o on  p.ProductId=o.ProductId   set  p.BuyerCount =p.BuyerCount+1   where o.OrderId=2014042514163700856;

UPDATE  product set BuyerCount =BuyerCount+1 where ProductId in(  SELECT ProductId from (SELECT ProductId from orderproductdetail where OrderId=2014042514163700856) as tttt );


 
 

　　

执行结果如下：

 

[SQL] UPDATE  product p, orderproductdetail o set  p.BuyerCount =p.BuyerCount+1    where   p.ProductId=o.ProductId and  o.OrderId=2014042514163700856;
受影响的行: 1
时间: 0.053ms

[SQL] 

UPDATE product p INNER JOIN orderproductdetail o on  p.ProductId=o.ProductId   set  p.BuyerCount =p.BuyerCount+1   where o.OrderId=2014042514163700856;
受影响的行: 1
时间: 0.053ms

[SQL] 

UPDATE  product set BuyerCount =BuyerCount+1 where ProductId in(  SELECT ProductId from (SELECT ProductId from orderproductdetail where OrderId=2014042514163700856) as tttt );
受影响的行: 1
时间: 0.072ms