CodingForums.com

CodingForums.com (http://www.codingforums.com/index.php)
-   MySQL (http://www.codingforums.com/forumdisplay.php?f=7)
-   -   More Efficient Query Needed (http://www.codingforums.com/showthread.php?t=289389)

sitechooser 03-07-2013 02:35 PM
More Efficient Query Needed
 
Hi All

Does anyone have an idea how to run this query...

Code:

SELECT *
FROM [TableName]
WHERE [ColumnName] IN
(
      SELECT [ColumnName]
      FROM [TableName]
      GROUP BY [ColumnName]
      HAVING COUNT(*) > 1
)
ORDER BY [ColumnName]
without the sub query?

The sub query option seems to be taking ages to run. Is there a more efficient query that does the same thing?

Thanks in advance for any help.

sunfighter 03-07-2013 03:32 PM
SELECT [ColumnName]
FROM [TableName]
GROUP BY [ColumnName] <= Not needed because that is the only thing your getting
HAVING COUNT(*) > 1 <= This makes no sense to me because count() tells you the number of rows. Do you mean where the value of the ColumnName column is large then 1?

sitechooser 03-07-2013 07:55 PM
More efficient query
 
Sorry, it would help if I explained what I'm trying to do!

The query is supposed to search a column (name) for duplicate values and then return all data in the table which has duplicate values in that column

Any ideas?

Old Pedant 03-07-2013 08:24 PM
Nope, I think you have it right.

Though doing it like this *MIGHT* be more efficient:
Code:
SELECT T1.*
FROM table AS T1,
    ( SELECT column, COUNT(column) AS cnt
      FROM table
      GROUP BY column
      HAVING cnt > 1
    ) AS T2
WHERE T1.column = T2.column
ORDER BY T1.column
Or even possibly (though I doubt it)
Code:
SELECT T1.*
FROM table AS T1,
    ( SELECT column, COUNT(column) AS cnt
      FROM table
      GROUP BY column
    ) AS T2
WHERE T1.column = T2.column AND T2.cnt > 1
ORDER BY T1.column
May not matter, but you could try it.

Old Pedant 03-07-2013 08:25 PM
It will, of course, make a *HUGE* difference if column is indexed.

sitechooser 03-07-2013 10:21 PM
Magic!

I ran the query on an 80MB table and it returned in 6.5 minutes. I then made the column concerned an index, ran the same query and it returned in 7.6 seconds! Bit of a difference:thumbsup:

Thanks a million for your help, got a critical meeting tomorrow morning and you'll make me look like superman.:D

Old Pedant 03-07-2013 11:26 PM
LOL! The cardinal rule of database design: When it's slow, index it.


All times are GMT +1. The time now is 11:52 AM.

Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.