0

I have a largish 27mil row table. The table contains a large number of duplicate string values due to case variance.

All rows have the string column and also one additional numerical column, and I am trying to identify duplicates, and then ignore the row with the numerically highest secondary value within each duplicate set and export the remaining rows to a new table.

I have tried a number of approaches, but performance has been so poor as to make them nonviable even as a one time event.

What is the most performant approach for this kind of duplicate search with logic?

Platform is MySQL.

1
  • Do you have any indexes now? Please provide SHOW CREATE TABLE.
    – Rick James
    Jul 23, 2016 at 4:00

1 Answer 1

2

I would try locating all duplicates and their respective highest secondary values like this:

SELECT
  StringColumn,
  MAX(SecondaryColumn) AS HighestValue
FROM
  atable
GROUP BY
  StringColumn
HAVING
  COUNT(*) > 1

and then using the results to produce the final set like this:

SELECT
  t.StringColumn,
  t.SecondaryColumn
FROM
  atable AS t
  INNER JOIN
  (
    SELECT
      StringColumn,
      MAX(SecondaryColumn) AS HighestValue
    FROM
      atable
    GROUP BY
      StringColumn
    HAVING
      COUNT(*) > 1
  ) AS s ON t.StringColumn = s.StringColumn
        AND t.SecondaryColumn <> s.HighestValue
;

I believe an index on (StringColumn, SecondaryColumn) should help the performance.

1
  • The first request ended up being very useful and performant with an index on the StringColumn. While the second would defiently get the data out that I was looking for it was big query time wise. The result of the first query was 800k rows from the 27M row original table. Those do not look like staggering amounts but, when you add the group to the mix is does seem to get a little out of hand time wise. This is certainly a correct answer, and a good answer for the question. In my case, I ended up using the first query, and a small script for the 2nd pass to get more control. Jul 28, 2016 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.