wjones
7eb2b445cb
- Major query performance boost: replaced correlated subqueries with DistinctBy() in BaseItemRepository, added episode deduplication index, and increased default command timeout to 120s. - Fixed LibraryMonitor shutdown race: added disposal checks and exception handling in FileRefresher to prevent ObjectDisposedException. - Added PowerShell and SQL utilities for fixing Linux paths in config files and database; new scripts for WebDir and path migration. - Auto-fix for WebDir in startup.json on load; new helper scripts and docs. - Added automated weekly DB performance monitoring scripts and reporting. - Expanded documentation and README for all fixes, scripts, and migration steps. - Updated SQL scripts for index creation and diagnostics; improved output handling. - Updated db-config defaults and added new diagnostic/report files.
129 lines
4.5 KiB
SQL
129 lines
4.5 KiB
SQL
-- Add Base Performance Indexes
|
|
-- These are the essential performance indexes that were in the original schema dump
|
|
-- but missing from the InitialCreate migration
|
|
-- Run this if migration doesn't apply automatically
|
|
\pset pager off
|
|
|
|
\echo 'Adding base performance indexes...';
|
|
|
|
-- ============================================================================
|
|
-- BaseItems Performance Indexes
|
|
-- ============================================================================
|
|
|
|
\echo 'Creating BaseItems performance indexes...';
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitems_communityrating_idx
|
|
ON library."BaseItems" ("CommunityRating" DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitems_datecreated_idx
|
|
ON library."BaseItems" ("DateCreated" DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitems_datemodified_idx
|
|
ON library."BaseItems" ("DateModified" DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitems_parentid_idx
|
|
ON library."BaseItems" ("ParentId", "Type");
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitems_premieredate_idx
|
|
ON library."BaseItems" ("PremiereDate" DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitems_productionyear_idx
|
|
ON library."BaseItems" ("ProductionYear" DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitems_seriespresentationuniquekey_idx
|
|
ON library."BaseItems" ("SeriesPresentationUniqueKey", "IndexNumber", "ParentIndexNumber");
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitems_sortname_idx
|
|
ON library."BaseItems" ("SortName");
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitems_topparentid_idx
|
|
ON library."BaseItems" ("TopParentId", "Type");
|
|
|
|
\echo '✓ BaseItems indexes created';
|
|
|
|
-- ============================================================================
|
|
-- BaseItemProviders Performance Indexes
|
|
-- ============================================================================
|
|
|
|
\echo 'Creating BaseItemProviders performance indexes...';
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitemproviders_providerid_idx
|
|
ON library."BaseItemProviders" ("ProviderId", "ItemId");
|
|
|
|
CREATE INDEX IF NOT EXISTS baseitemproviders_providervalue_idx
|
|
ON library."BaseItemProviders" ("ProviderValue", "ProviderId");
|
|
|
|
\echo '✓ BaseItemProviders indexes created';
|
|
|
|
-- ============================================================================
|
|
-- MediaStreamInfos Performance Indexes
|
|
-- ============================================================================
|
|
|
|
\echo 'Creating MediaStreamInfos performance indexes...';
|
|
|
|
CREATE INDEX IF NOT EXISTS mediastreaminfos_codec_idx
|
|
ON library."MediaStreamInfos" ("Codec");
|
|
|
|
CREATE INDEX IF NOT EXISTS mediastreaminfos_itemid_idx
|
|
ON library."MediaStreamInfos" ("ItemId", "StreamType");
|
|
|
|
\echo '✓ MediaStreamInfos indexes created';
|
|
|
|
-- ============================================================================
|
|
-- PeopleBaseItemMap Performance Indexes
|
|
-- ============================================================================
|
|
|
|
\echo 'Creating PeopleBaseItemMap performance indexes...';
|
|
|
|
CREATE INDEX IF NOT EXISTS peoplebaseitemmap_itemid_idx
|
|
ON library."PeopleBaseItemMap" ("ItemId", "PeopleId");
|
|
|
|
CREATE INDEX IF NOT EXISTS peoplebaseitemmap_peopleid_idx
|
|
ON library."PeopleBaseItemMap" ("PeopleId", "ItemId");
|
|
|
|
\echo '✓ PeopleBaseItemMap indexes created';
|
|
|
|
-- ============================================================================
|
|
-- UserData Performance Indexes
|
|
-- ============================================================================
|
|
|
|
\echo 'Creating UserData performance indexes...';
|
|
|
|
CREATE INDEX IF NOT EXISTS userdata_lastplayeddate_idx
|
|
ON library."UserData" ("LastPlayedDate" DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS userdata_userid_isfavorite_idx
|
|
ON library."UserData" ("UserId", "IsFavorite");
|
|
|
|
CREATE INDEX IF NOT EXISTS userdata_userid_played_idx
|
|
ON library."UserData" ("UserId", "Played");
|
|
|
|
\echo '✓ UserData indexes created';
|
|
|
|
-- ============================================================================
|
|
-- Update Statistics
|
|
-- ============================================================================
|
|
|
|
\echo 'Updating table statistics...';
|
|
|
|
ANALYZE library."BaseItems";
|
|
ANALYZE library."BaseItemProviders";
|
|
ANALYZE library."MediaStreamInfos";
|
|
ANALYZE library."PeopleBaseItemMap";
|
|
ANALYZE library."UserData";
|
|
|
|
\echo '';
|
|
\echo '========================================';
|
|
\echo '✓ Base Performance Indexes Created!';
|
|
\echo '========================================';
|
|
\echo '';
|
|
\echo 'Created 18 base performance indexes:';
|
|
\echo ' - 9 on BaseItems';
|
|
\echo ' - 2 on BaseItemProviders';
|
|
\echo ' - 2 on MediaStreamInfos';
|
|
\echo ' - 2 on PeopleBaseItemMap';
|
|
\echo ' - 3 on UserData';
|
|
\echo '';
|
|
\echo 'These indexes are essential for good performance.';
|
|
\echo 'Restart Jellyfin to see improvements.';
|