MySQL - SELECT WHERE field IN subquery - Extremely slow why

Question

I ve got a couple of duplicates in a database that I want to inspect  so what I did to see which are duplicates  I did this   SELECT relevant field FROM some table GROUP BY relevant field HAVING COUNT     gt  1   This way  I will get all rows with relevant field occuring more than once  This query takes milliseconds to execute   Now  I wanted to inspect each of the duplicates  so I thought I could SELECT each row in some table with a relevant field in the above query  so I did like this   SELECT   FROM some table  WHERE relevant field IN       SELECT relevant field     FROM some table     GROUP BY relevant field     HAVING COUNT     gt  1     This turns out to be extreeeemely slow for some reason  it takes minutes   What exactly is going on here to make it that slow  relevant field is indexed   Eventually I tried creating a view  temp view  from the first query  SELECT relevant field FROM some table GROUP BY relevant field HAVING COUNT     gt  1   and then making my second query like this instead   SELECT   FROM some table WHERE relevant field IN       SELECT relevant field     FROM temp view     And that works just fine  MySQL does this in some milliseconds   Any SQL experts here who can explain what s going on

User · Answer

Firstly you can find duplicate rows and find count of rows is used how many times and order it by number like this;

_x000D_

SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,_x000D_
(_x000D_
  CASE q.NID_x000D_
  WHEN @curCode THEN_x000D_
   @curRow := @curRow + 1_x000D_
  ELSE_x000D_
   @curRow := 1_x000D_
  AND @curCode := q.NID_x000D_
  END_x000D_
 ) AS No_x000D_
FROM UserInfo q,_x000D_
(_x000D_
  SELECT_x000D_
   @curRow := 1,_x000D_
   @curCode := ''_x000D_
 ) rt_x000D_
WHERE q.NID IN_x000D_
(_x000D_
    SELECT NID_x000D_
    FROM UserInfo_x000D_
    GROUP BY NID_x000D_
    HAVING COUNT(*) > 1_x000D_
)

_x000D_

after that create a table and insert result to it.

_x000D_

create table CopyTable _x000D_
SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,_x000D_
(_x000D_
  CASE q.NID_x000D_
  WHEN @curCode THEN_x000D_
   @curRow := @curRow + 1_x000D_
  ELSE_x000D_
   @curRow := 1_x000D_
  AND @curCode := q.NID_x000D_
  END_x000D_
 ) AS No_x000D_
FROM UserInfo q,_x000D_
(_x000D_
  SELECT_x000D_
   @curRow := 1,_x000D_
   @curCode := ''_x000D_
 ) rt_x000D_
WHERE q.NID IN_x000D_
(_x000D_
    SELECT NID_x000D_
    FROM UserInfo_x000D_
    GROUP BY NID_x000D_
    HAVING COUNT(*) > 1_x000D_
)

_x000D_

Finally, delete dublicate rows.No is start 0. Except fist number of each group delete all dublicate rows.

_x000D_

delete from  CopyTable where No!= 0;

_x000D_

User · Answer

sometimes when data grow bigger mysql WHERE IN s could be pretty slow because of query optimization  Try using STRAIGHT JOIN to tell mysql to execute query as is  e g    SELECT STRAIGHT JOIN table field FROM table WHERE table id IN         but beware  in most cases mysql optimizer works pretty well  so I would recommend to use it only when you have this kind of problem

User · Answer

Subqueries vs joins  http   www scribd com doc 2546837 New-Subquery-Optimizations-In-MySQL-6

User · Answer

I have reformatted your slow sql query with www prettysql net  SELECT   FROM some table WHERE  relevant field in      SELECT relevant field   FROM some table   GROUP BY relevant field   HAVING COUNT        gt  1       When using a table in both the query and the subquery  you should always alias both  like this   SELECT   FROM some table as t1 WHERE  t1 relevant field in      SELECT t2 relevant field   FROM some table as t2   GROUP BY t2 relevant field   HAVING COUNT   t2 relevant field    gt  1       Does that help

User · Answer

Try this  SELECT t1   FROM   some table t1     SELECT relevant field   FROM some table   GROUP BY relevant field   HAVING COUNT      gt  1  t2 WHERE  t1 relevant field   t2 relevant field

User · Answer

This is similar to my case  where I have a table named tabel buku besar  What I need are   Looking for record that have account code  101 100  in tabel buku besar which have companyarea  20000  and also have IDR as currency I need to get all record from tabel buku besar which have account code same as step 1 but have transaction number in step 1 result      while using select     from   where    transaction number in  select transaction number from        my query running extremely slow and sometimes causing request time out or make my application not responding     I try this combination and the result   not bad      select DATE FORMAT L TANGGAL INPUT   d- m- y   AS TANGGAL        L TRANSACTION NUMBER AS VOUCHER        L ACCOUNT CODE        C DESCRIPTION        L DEBET        L KREDIT   from  select   from tabel buku besar A                 where A COMPANYAREA   COMPANYAREA                        AND A CURRENCY   Currency                        AND A ACCOUNT CODE    ACCOUNT                        AND  A TANGGAL INPUT BETWEEN STR TO DATE   StartDate    d  m  Y   AND STR TO DATE   EndDate    d  m  Y     L  INNER JOIN  select   from tabel buku besar A                      where A COMPANYAREA   COMPANYAREA                             AND A CURRENCY   Currency                             AND A ACCOUNT CODE   ACCOUNT                             AND  A TANGGAL INPUT BETWEEN STR TO DATE   StartDate    d  m  Y   AND STR TO DATE   EndDate    d  m  Y     R ON R TRANSACTION NUMBER L TRANSACTION NUMBER AND R COMPANYAREA L COMPANYAREA  LEFT OUTER JOIN master account C ON C ACCOUNT CODE L ACCOUNT CODE AND C COMPANYAREA L COMPANYAREA  ORDER BY L TANGGAL INPUT L TRANSACTION NUMBER

User · Answer

Rewrite the query into this  SELECT st1    st2 relevant field FROM sometable st1 INNER JOIN sometable st2 ON  st1 relevant field   st2 relevant field  GROUP BY st1 id     list a unique sometable field here   HAVING COUNT     gt  1   I think st2 relevant field must be in the select  because otherwise the having clause will give an error  but I m not 100  sure  Never use IN with a subquery  this is notoriously slow  Only ever use IN with a fixed list of values    More tips     If you want to make queries faster  don t do a SELECT   only select the fields that you really need  Make sure you have an index on relevant field to speed up the equi-join  Make sure to group by on the primary key    If you are on InnoDB and you only select indexed fields  and things are not too complex  than MySQL will resolve your query using only the indexes  speeding things way up    General solution for 90  of your IN  select  queries  Use this code  SELECT   FROM sometable a WHERE EXISTS     SELECT 1 FROM sometable b   WHERE a relevant field   b relevant field   GROUP BY b relevant field   HAVING count     gt  1

User · Answer

The subquery is being run for each row because it is a correlated query  One can make a correlated query into a non-correlated query by selecting everything from the subquery  like so   SELECT   FROM       SELECT relevant field     FROM some table     GROUP BY relevant field     HAVING COUNT     gt  1   AS subquery   The final query would look like this   SELECT   FROM some table WHERE relevant field IN       SELECT   FROM               SELECT relevant field         FROM some table         GROUP BY relevant field         HAVING COUNT     gt  1       AS subquery

User · Answer

SELECT st1   FROM some table st1 inner join        SELECT relevant field     FROM some table     GROUP BY relevant field     HAVING COUNT     gt  1  st2 on st2 relevant field   st1 relevant field    I ve tried your query on one of my databases  and also tried it rewritten as a join to a sub-query   This worked a lot faster  try it

User · Answer

I find this to be the most efficient for finding if a value exists  logic can easily be inverted to find if a value doesn t exist  ie IS NULL    SELECT   FROM primary table st1 LEFT JOIN comparision table st2 ON  st1 relevant field   st2 relevant field  WHERE st2 primaryKey IS NOT NULL    Replace relevant field with the name of the value that you want to check exists in your table   Replace primaryKey with the name of the primary key column on the comparison table

[mysql] MySQL - SELECT WHERE field IN (subquery) - Extremely slow why?

Examples related to mysql

Examples related to subquery

Examples related to where-in