banshee r3027 - in trunk/banshee: . src/Core/Banshee.Services/Banshee.Collection.Database src/Core/Banshee.Services/Banshee.Database

[gburt svn gnome org]

Author: gburt
Date: Sat Jan 26 02:28:16 2008
New Revision: 3027
URL: http://svn.gnome.org/viewvc/banshee?rev=3027&view=rev

Log:
2008-01-25  Gabriel Burt  <gabriel burt gmail com>

	* src/Core/Banshee.Services/Banshee.Collection.Database/AlbumListDatabaseModel.cs:
	* src/Core/Banshee.Services/Banshee.Collection.Database/ArtistListDatabaseModel.cs:
	Get rid of unnecessary disctinct () call in query.

	* src/Core/Banshee.Services/Banshee.Database/BansheeDbFormatMigrator.cs:
	Add some more indices.


Modified:
   trunk/banshee/ChangeLog
   trunk/banshee/src/Core/Banshee.Services/Banshee.Collection.Database/AlbumListDatabaseModel.cs
   trunk/banshee/src/Core/Banshee.Services/Banshee.Collection.Database/ArtistListDatabaseModel.cs
   trunk/banshee/src/Core/Banshee.Services/Banshee.Database/BansheeDbFormatMigrator.cs

Modified: trunk/banshee/src/Core/Banshee.Services/Banshee.Collection.Database/AlbumListDatabaseModel.cs
==============================================================================
--- trunk/banshee/src/Core/Banshee.Services/Banshee.Collection.Database/AlbumListDatabaseModel.cs	(original)
+++ trunk/banshee/src/Core/Banshee.Services/Banshee.Collection.Database/AlbumListDatabaseModel.cs	Sat Jan 26 02:28:16 2008
@@ -78,7 +78,7 @@
                     track_model == null ? null :
                         String.Format (@"
                             CoreAlbums.AlbumID IN
-                                (SELECT DISTINCT(CoreTracks.AlbumID) FROM CoreTracks, CoreAlbums, CoreCache
+                                (SELECT CoreTracks.AlbumID FROM CoreTracks, CoreAlbums, CoreCache
                                     WHERE CoreCache.ModelID = {0} AND
                                           CoreCache.ItemId = CoreTracks.TrackID AND
                                           CoreAlbums.AlbumID = CoreTracks.AlbumID)",

Modified: trunk/banshee/src/Core/Banshee.Services/Banshee.Collection.Database/ArtistListDatabaseModel.cs
==============================================================================
--- trunk/banshee/src/Core/Banshee.Services/Banshee.Collection.Database/ArtistListDatabaseModel.cs	(original)
+++ trunk/banshee/src/Core/Banshee.Services/Banshee.Collection.Database/ArtistListDatabaseModel.cs	Sat Jan 26 02:28:16 2008
@@ -68,7 +68,7 @@
                     @"FROM CoreArtists {0} ORDER BY Name",
                     track_model != null ? String.Format(@"
                         WHERE CoreArtists.ArtistID IN
-                            (SELECT DISTINCT (CoreTracks.ArtistID) FROM CoreTracks, CoreArtists, CoreCache
+                            (SELECT CoreTracks.ArtistID FROM CoreTracks, CoreArtists, CoreCache
                                 WHERE CoreCache.ModelID = {0} AND
                                       CoreCache.ItemId = CoreTracks.TrackID AND
                                       CoreArtists.ArtistId = CoreTracks.ArtistID)",

Modified: trunk/banshee/src/Core/Banshee.Services/Banshee.Database/BansheeDbFormatMigrator.cs
==============================================================================
--- trunk/banshee/src/Core/Banshee.Services/Banshee.Database/BansheeDbFormatMigrator.cs	(original)
+++ trunk/banshee/src/Core/Banshee.Services/Banshee.Database/BansheeDbFormatMigrator.cs	Sat Jan 26 02:28:16 2008
@@ -275,6 +275,10 @@
                     DateAddedStamp      INTEGER
                 )
             ");
+            Execute("CREATE INDEX CoreTracksArtistIndex ON CoreTracks(ArtistID)");
+            Execute("CREATE INDEX CoreTracksAlbumIndex  ON CoreTracks(AlbumID)");
+            Execute("CREATE INDEX CoreTracksRatingIndex ON CoreTracks(Rating)");
+            Execute("CREATE INDEX CoreTracksTrackNumberIndex ON CoreTracks(AlbumID, TrackNumber)");
             
             Execute(@"
                 CREATE TABLE CoreAlbums (
@@ -293,6 +297,8 @@
                     Rating              INTEGER
                 )
             ");
+            Execute("CREATE INDEX CoreAlbumsIndex       ON CoreAlbums(Title)");
+            Execute("CREATE INDEX CoreAlbumsArtistID    ON CoreAlbums(ArtistID)");
             
             Execute(@"
                 CREATE TABLE CoreArtists (
@@ -303,6 +309,7 @@
                     Rating              INTEGER
                 )
             ");
+            Execute("CREATE INDEX CoreArtistsIndex      ON CoreArtists(Name)");
             
             Execute(@"
                 CREATE TABLE CorePlaylists (
@@ -356,11 +363,9 @@
                     ItemID              INTEGER
                 )
             ");
-
-            Execute("CREATE INDEX CoreTracksArtistIndex ON CoreTracks(ArtistID)");
-            Execute("CREATE INDEX CoreTracksAlbumIndex  ON CoreTracks(AlbumID)");
-            Execute("CREATE INDEX CoreArtistsIndex      ON CoreArtists(Name)");
-            Execute("CREATE INDEX CoreAlbumsIndex       ON CoreAlbums(Title)");
+            // This index slows down queries were we shove data into the CoreCache.
+            // Since we do that frequently, not using it.
+            //Execute("CREATE INDEX CoreCacheModelId      ON CoreCache(ModelID)");
         }
         
         private void MigrateFromLegacyBanshee()