Improve performance on RemoveDuplicatesFromRoutes migrationfix/25635

1 files changed, 10 insertions, 14 deletions

diff --git a/db/migrate/20161202152031_remove_duplicates_from_routes.rb b/db/migrate/20161202152031_remove_duplicates_from_routes.rb
index a21bde69995..d73b0847506 100644
--- a/db/migrate/20161202152031_remove_duplicates_from_routes.rb
+++ b/db/migrate/20161202152031_remove_duplicates_from_routes.rb
@@ -12,20 +12,16 @@ class RemoveDuplicatesFromRoutes < ActiveRecord::Migration
     # to fill these values that avoid duplicate entries in the routes table.
     return unless Gitlab::Database.mysql?
 
-    select_all("SELECT path FROM #{quote_table_name(:routes)} GROUP BY path HAVING COUNT(*) > 1").each do |row|
-      path = connection.quote(row['path'])
-      execute(%Q{
-        DELETE FROM #{quote_table_name(:routes)}
-        WHERE path = #{path}
-        AND id != (
-          SELECT id FROM (
-            SELECT max(id) AS id
-            FROM #{quote_table_name(:routes)}
-            WHERE path = #{path}
-          ) max_ids
-        )
-      })
-    end
+    execute <<-EOF
+      DELETE duplicated_rows.*
+      FROM routes AS duplicated_rows
+        INNER JOIN (
+          SELECT path, MAX(id) as max_id
+          FROM routes
+          GROUP BY path
+          HAVING COUNT(*) > 1
+        ) AS good_rows ON good_rows.path = duplicated_rows.path AND good_rows.max_id <> duplicated_rows.id;
+    EOF
   end
 
   def down