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 …
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
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?
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)
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