2个不同的小型查询与1个带有子查询的查询 [英] 2 different small query vs 1 query with subquery

问题描述

我有这样的桌子

name       | personal_number 
-----------------------------------------
Jon        | 222
Alex       | 555
Jon        | 222
Jimmy      | 999

我需要获取每个名称，该名称在表中重复的person_number大于1，结果必须是:

I need get every name, which personal_number repeates in table more than 1, that is result must be:

 Jon        
 Jon        

所以，变体1):

SELECT  name  FROM mytable WHERE personal_number IN (
        SELECT  personal_number  FROM mytable  GROUP BY personal_number
        HAVING COUNT(*) > 1
)

变体2):

SELECT  personal_number  FROM mytable  GROUP BY personal_number
        HAVING COUNT(*) > 1
)

然后，使用php，将检索到的personal_numbers作为字符串连接(这样的'222', '222')并运行其他查询

Then, using php, retrieved personal_numbers join as string (soemthing like this '222', '222' ) and run other query

SELECT  name FROM mytable  WHERE personal_number IN( here joined string )

变体2的运行速度比变体1快10倍，这让我感到惊讶，我当时以为一个查询会更快，但是...

Variant 2 works approximately 10 times faster, than variant 1, this is surprise for me, I was thinking that one query will be faster, but...

(在表中有50万行，列personal_number未被索引)

(In table is 500 000 rows, column personal_number not indexed)

那么，您对这种情况的意思是什么?为什么变体2比变体1快很多?

So, what you mean about cases like this? why variant 2 is many faster than variant 1 ?

推荐答案

如本文所述您应避免使用子查询，而应使用联接.

You should try to avoid having subqueries and use joining instead.

这篇关于2个不同的小型查询与1个带有子查询的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！