SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Why is this query taking forever to run??

    Hi,

    I have a table with a few 100K records. I have an index on column PiNumber - but when I run the following query in phpmyadmin it will run for
    30+ minutes ...

    Code MySQL:
    SELECT PiNumber
    FROM data_table 
    WHERE PiNumber IN (  
        SELECT PiNumber  
        FROM data_table  
        GROUP BY PiNumber 
        HAVING COUNT(PiNumber) > 1  
        )  
    ORDER BY PiNumber

    But ... when I just do this simple query it runs in under a second

    Code MySQL:
    SELECT PiNumber 
    FROM data_table
    GROUP BY PiNumber
    HAVING COUNT( PiNumber ) >1

    When i run show processlist, the query is stuck in state 'Sending data'
    any thoughts/suggestions?

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why do you need the first query at all? Why not just add the order by to the correct second query?

  3. #3
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guelphdad View Post
    Why not just add the order by to the correct second query?
    precisely

    in fact, why bother with the ORDER BY at all? grouping typically sorts results into ascending sequence anyway

    but if you really need it, mysql has a non-standard extension that lets you put it on the GROUP BY clause --
    Code:
    SELECT PiNumber 
      FROM data_table
    GROUP 
        BY PiNumber ASC
    HAVING COUNT(*) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The first query that was not working was to list all occurrences of the duplicate PiNumber
    The second query that was working was only producing One Occurrence of the duplicate PiNumber

    I just realized in my initial post i did not even write what I was trying to do with the query, lol
    But I got what I need using the following (which lists all occurrences of the duplicate PiNumber)

    Code MySQL:
    SELECT t1.*
    FROM data_table t1
    INNER JOIN (
    SELECT PiNumber
    FROM data_table
    GROUP BY PiNumber
    HAVING COUNT(PiNumber) > 1
    ) t2 ON
    t1.PiNumber = t2.PiNumber

    Thanks

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Do you have any other fields on that table that are unique to the row, like an id or something? If so, you can use that to find duplicates like so:

    Code sql:
    SELECT
       t1.id, t2.id
    FROM
       data_table t1
       INNER JOIN
       data_table t2
       ON
          t1.piNumber=t2.piNumber
          AND
          t1.id <> t2.id

    Which will return all duplicate pairs in the table and should be a lot faster than what you are doing.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •