子查询的运行速度比联接快? [英] Subquery is running faster than join?

问题描述

我正在尝试从同一列的另一列中确实存在的一列中识别值

I am trying to identify value from one column which does exist in another column in same table

子查询

SELECT DISTINCT `Wear it With - Outfits 1` 
  FROM `product list` 
 WHERE `Wear it With - Outfits 1` NOT IN (SELECT `sku` 
                                            FROM `product list`)

...返回结果为2.7287秒

...returns result in 2.7287sec

我尝试用左联接替换子查询

I tried to replace sub query by left join

   SELECT DISTINCT table1.`Wear it With - Outfits 1`
     FROM `product list` as table1 
LEFT JOIN `product list` as table2 ON table1.`Wear it With - Outfits 1`=table2.sku
     WHERE table2.sku IS NULL 
       AND table1.`Wear it With - Outfits 1` IS NOT NULL

...在5.7651秒内返回结果

...which returns result in 5.7651 sec

通常联接返回结果要快得多.所以我相信我在查询中做了一些有趣的事情? 但是找不到任何原因使我的子查询运行得比

Normally joins return results much faster. So i believe i did something funny in my query? But can not find any reason why my subquery is running faster than

推荐答案

语句通常加入返回结果要快得多".是愚蠢的，尤其是在没有引用任何特定数据库系统的情况下.

The statement 'Normally joins return results much faster.' is silly, especially without reference to any particular database system.

许多因素影响确定特定查询的性能.您可以在所使用的任何数据库产品中使用EXPLAIN工具来确定在这种情况下为什么最好使用子查询(提示:它可能与关键字DISTINCT有关).

Many factors go into determining the performance of a particular query. You can use the EXPLAIN tool in whatever database product you're using to determine exactly why the sub-query is preferable in this case (hint: it likely has to do with the use of the keyword DISTINCT).

这篇关于子查询的运行速度比联接快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！