aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSascha Woo <sascha.woo@gmail.com>2015-04-14 16:55:35 +0200
committerSascha Woo <sascha.woo@gmail.com>2015-04-14 16:55:35 +0200
commita249e889cd1af8883424b6873f23b684cde41de4 (patch)
treeaf1a1c0b081cbfeceae6a7a99039c6732533f4da
parent6def365ddeb10fa9ef0b50df2862c0001b794932 (diff)
parenta3761ac45db6d32612e18227a19eb04b811b6d0b (diff)
Merge pull request #6941 from xhaggi/musicdb-albumview-improve-performance
[musicdb] improves performance for querying the album view
-rw-r--r--xbmc/music/MusicDatabase.cpp33
1 files changed, 21 insertions, 12 deletions
diff --git a/xbmc/music/MusicDatabase.cpp b/xbmc/music/MusicDatabase.cpp
index 0eacb6083c..a6fd7d5805 100644
--- a/xbmc/music/MusicDatabase.cpp
+++ b/xbmc/music/MusicDatabase.cpp
@@ -306,12 +306,10 @@ void CMusicDatabase::CreateViews()
" album.strImage as strImage, "
" iRating, "
" bCompilation, "
- " MIN(song.iTimesPlayed) AS iTimesPlayed, "
+ " (SELECT MIN(song.iTimesPlayed) FROM song WHERE song.idAlbum = album.idAlbum) AS iTimesPlayed, "
" strReleaseType "
"FROM album"
- " LEFT OUTER JOIN song ON"
- " album.idAlbum=song.idAlbum "
- "GROUP BY album.idAlbum");
+ );
CLog::Log(LOGINFO, "create artist view");
m_pDS->exec("CREATE VIEW artistview AS SELECT"
@@ -4079,7 +4077,7 @@ void CMusicDatabase::UpdateTables(int version)
int CMusicDatabase::GetSchemaVersion() const
{
- return 51;
+ return 52;
}
unsigned int CMusicDatabase::GetSongIDs(const Filter &filter, vector<pair<int,int> > &songIDs)
@@ -5759,13 +5757,24 @@ bool CMusicDatabase::GetFilter(CDbUrl &musicUrl, Filter &filter, SortDescription
option = options.find("artistid");
if (option != options.end())
{
- filter.AppendJoin("JOIN song ON song.idAlbum = albumview.idAlbum "
- "JOIN song_artist ON song.idSong = song_artist.idSong "
- "JOIN album_artist ON albumview.idAlbum = album_artist.idAlbum");
- filter.AppendWhere(PrepareSQL(" song_artist.idArtist = %i" // All albums linked to this artist via songs
- " OR album_artist.idArtist = %i", // All albums where album artists fit
- (int)option->second.asInteger(), (int)option->second.asInteger()));
- filter.AppendGroup("albumview.idAlbum");
+ int idArtist = static_cast<int>(option->second.asInteger());
+ filter.AppendWhere(PrepareSQL(
+ "(EXISTS ( "
+ " SELECT 1 "
+ " FROM song "
+ " JOIN song_artist ON song.idSong = song_artist.idSong "
+ " WHERE song.idAlbum = albumview.idAlbum"
+ " AND song_artist.idArtist = %i "
+ ") OR "
+ "EXISTS ( "
+ " SELECT 1 "
+ " FROM album_artist "
+ " WHERE album_artist.idAlbum = albumview.idAlbum "
+ " AND album_artist.idArtist = %i "
+ "))",
+ idArtist,
+ idArtist
+ ));
}
else
{