Merge pull request #16062 from Shadowghost/perf-rebased

Query Performance Improvements

1 files changed, 20 insertions, 3 deletions

diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
index 6fccfd976d..7fe1836c42 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
@@ -28,15 +28,17 @@ public class BaseItemConfiguration : IEntityTypeConfiguration<BaseItemEntity>
         builder.HasMany(e => e.Parents);
         builder.HasMany(e => e.Children);
         builder.HasMany(e => e.DirectChildren).WithOne(e => e.DirectParent).HasForeignKey(e => e.ParentId).OnDelete(DeleteBehavior.Cascade);
+        builder.HasMany(e => e.Extras).WithOne(e => e.Owner).HasForeignKey(e => e.OwnerId).OnDelete(DeleteBehavior.NoAction);
         builder.HasMany(e => e.LockedFields);
         builder.HasMany(e => e.TrailerTypes);
         builder.HasMany(e => e.Images);
 
         builder.HasIndex(e => e.Path);
         builder.HasIndex(e => e.ParentId);
+        builder.HasIndex(e => e.OwnerId);
+        builder.HasIndex(e => e.Name);
+        builder.HasIndex(e => new { e.ExtraType, e.OwnerId });
         builder.HasIndex(e => e.PresentationUniqueKey);
-        builder.HasIndex(e => new { e.Id, e.Type, e.IsFolder, e.IsVirtualItem });
-
         // covering index
         builder.HasIndex(e => new { e.TopParentId, e.Id });
         // series
@@ -53,14 +55,29 @@ public class BaseItemConfiguration : IEntityTypeConfiguration<BaseItemEntity>
         // latest items
         builder.HasIndex(e => new { e.Type, e.TopParentId, e.IsVirtualItem, e.PresentationUniqueKey, e.DateCreated });
         builder.HasIndex(e => new { e.IsFolder, e.TopParentId, e.IsVirtualItem, e.PresentationUniqueKey, e.DateCreated });
+        // latest items - optimized for sorting by DateCreated (no PresentationUniqueKey breaking the sort)
+        builder.HasIndex(e => new { e.TopParentId, e.Type, e.IsVirtualItem, e.DateCreated });
+        builder.HasIndex(e => new { e.TopParentId, e.IsFolder, e.IsVirtualItem, e.DateCreated });
+        builder.HasIndex(e => new { e.TopParentId, e.MediaType, e.IsVirtualItem, e.DateCreated });
         // resume
         builder.HasIndex(e => new { e.MediaType, e.TopParentId, e.IsVirtualItem, e.PresentationUniqueKey });
+        // sorted library queries (e.g., Series sorted by SortName)
+        builder.HasIndex(e => new { e.Type, e.TopParentId, e.SortName });
+        // NextUp: per-series episode ordering (index seek + range scan on season/episode)
+        builder.HasIndex(e => new { e.Type, e.SeriesPresentationUniqueKey, e.ParentIndexNumber, e.IndexNumber });
+        // ByName queries: WHERE Type = X AND CleanName IN (...)
+        builder.HasIndex(e => new { e.Type, e.CleanName });
+        // Latest TV: GROUP BY SeriesName
+        builder.HasIndex(e => e.SeriesName);
+        // Latest TV: episode count per season, season count per series
+        builder.HasIndex(e => e.SeasonId);
+        builder.HasIndex(e => e.SeriesId);
 
         builder.HasData(new BaseItemEntity()
         {
             Id = Guid.Parse("00000000-0000-0000-0000-000000000001"),
             Type = "PLACEHOLDER",
-            Name = "This is a placeholder item for UserData that has been detacted from its original item",
+            Name = "This is a placeholder item for UserData that has been detached from its original item",
         });
     }
 }