Geek Answers Handbook

Alternative 'where col in (list)' for MySQL

Hi, I have the following table T:

id    1   2    3    4
col   a   b    a    c

I want to make a choice that returns id, col when group by (col) has a counter (col)> 1

One way to do this is

SELECT id,col FROM T 
   WHERE col IN (SELECT col FROM T GROUP BY(col) HAVING COUNT(col)>1);

Inter-choice (right) returns 'a', and the main (left) returns 1, a and 3, a

The problem is that the in in statement looks very slow. In my real case, the results of internal selection have a lot of “col”, about 70,000, and it takes several hours.

At present, it is much faster to make an internal choice, and the main one is to get all identifiers and upcs and local intersection. MySQL should be able to handle this kind of query efficiently.

Can I replace the connection point or something faster?

thank

+5
2

, INNER JOIN

  • , col
  • col, id

SQL

SELECT  T.id, T.col
FROM    T
        INNER JOIN (
          SELECT   col
          FROM     T
          GROUP BY col
          HAVING COUNT(*) > 1
        ) tcol ON tcol.col = T.col
+5
SELECT  id, col
FROM    t t1
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    t t2
        WHERE   t2.col = t1.col
                AND t2.id <> t1.id
        )

, (col) ( InnoDB) (col, id) ( MyISAM)

, . , .

+2

More articles:


All Articles