Backport pull request #18073 from jellyfin/release-12.z

Fix ChangeOwnerIdToGuid migration performance

Original-merge: cbee6bd93da515172605b39c82462de2e7a66cd4

Merged-by: crobibero <cody@robibe.ro>

Backported-by: Cody Robibero <cody@robibe.ro>

1 files changed, 18 insertions, 37 deletions

diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs
index 379da0e9be..8c03347736 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs
@@ -33,12 +33,24 @@ namespace Jellyfin.Database.Providers.Sqlite.Migrations
                 -- deletes an item: reattach it to the placeholder item instead of letting the
                 -- FK_UserData_BaseItems_ItemId cascade wipe it. The placeholder can only hold one row
                 -- per (UserId, CustomDataKey), so resolve collisions before repointing anything.
+                DROP TABLE IF EXISTS "DoomedUserDataKeys";
+                CREATE TEMPORARY TABLE "DoomedUserDataKeys" (
+                    "UserId" TEXT NOT NULL,
+                    "CustomDataKey" TEXT NOT NULL,
+                    PRIMARY KEY ("UserId", "CustomDataKey"));
+
+                -- Collect the colliding keys up front: correlating against "UserData" directly makes
+                -- the delete below re-scan every row of that user once per placeholder row.
+                INSERT OR IGNORE INTO "DoomedUserDataKeys" ("UserId", "CustomDataKey")
+                SELECT Doomed."UserId", Doomed."CustomDataKey"
+                FROM "UserData" AS Doomed
+                INNER JOIN "OrphanedBaseItemIds" AS Orphan ON Orphan."Id" = Doomed."ItemId";
+
                 DELETE FROM "UserData"
                 WHERE "ItemId" = '00000000-0000-0000-0000-000000000001'
                   AND EXISTS (
                       SELECT 1
-                      FROM "UserData" AS Doomed
-                      INNER JOIN "OrphanedBaseItemIds" AS Orphan ON Orphan."Id" = Doomed."ItemId"
+                      FROM "DoomedUserDataKeys" AS Doomed
                       WHERE Doomed."UserId" = "UserData"."UserId"
                         AND Doomed."CustomDataKey" = "UserData"."CustomDataKey");
 
@@ -63,6 +75,7 @@ namespace Jellyfin.Database.Providers.Sqlite.Migrations
 
                 DELETE FROM "BaseItems" WHERE "Id" IN (SELECT "Id" FROM "OrphanedBaseItemIds");
 
+                DROP TABLE "DoomedUserDataKeys";
                 DROP TABLE "OrphanedBaseItemIds";
                 """);
 
@@ -106,41 +119,9 @@ namespace Jellyfin.Database.Providers.Sqlite.Migrations
                 columns: new[] { "BaseItemEntityId", "Name", "OwnerId" },
                 values: new object[] { null, "This is a placeholder item for UserData that has been detached from its original item", null });
 
-            migrationBuilder.CreateIndex(
-                name: "IX_BaseItems_BaseItemEntityId",
-                table: "BaseItems",
-                column: "BaseItemEntityId");
-
-            migrationBuilder.CreateIndex(
-                name: "IX_BaseItems_ExtraType",
-                table: "BaseItems",
-                column: "ExtraType");
-
-            migrationBuilder.CreateIndex(
-                name: "IX_BaseItems_ExtraType_OwnerId",
-                table: "BaseItems",
-                columns: new[] { "ExtraType", "OwnerId" });
-
-            migrationBuilder.CreateIndex(
-                name: "IX_BaseItems_OwnerId",
-                table: "BaseItems",
-                column: "OwnerId");
-
-            migrationBuilder.CreateIndex(
-                name: "IX_BaseItems_TopParentId_IsFolder_IsVirtualItem_DateCreated",
-                table: "BaseItems",
-                columns: new[] { "TopParentId", "IsFolder", "IsVirtualItem", "DateCreated" });
-
-            migrationBuilder.CreateIndex(
-                name: "IX_BaseItems_TopParentId_MediaType_IsVirtualItem_DateCreated",
-                table: "BaseItems",
-                columns: new[] { "TopParentId", "MediaType", "IsVirtualItem", "DateCreated" });
-
-            migrationBuilder.CreateIndex(
-                name: "IX_BaseItems_TopParentId_Type_IsVirtualItem_DateCreated",
-                table: "BaseItems",
-                columns: new[] { "TopParentId", "Type", "IsVirtualItem", "DateCreated" });
-
+            // No CreateIndex calls here on purpose: AddForeignKey rebuilds BaseItems on SQLite and
+            // recreates every index of the target model afterwards, so building them first only
+            // pays for a full index pass that the rebuild immediately throws away.
             migrationBuilder.AddForeignKey(
                 name: "FK_BaseItems_BaseItems_BaseItemEntityId",
                 table: "BaseItems",