diff options
| author | xhaggi <sascha.woo@gmail.com> | 2015-04-13 14:53:34 +0200 |
|---|---|---|
| committer | xhaggi <sascha.woo@gmail.com> | 2015-04-14 10:16:15 +0200 |
| commit | a3761ac45db6d32612e18227a19eb04b811b6d0b (patch) | |
| tree | c96a27d7718ef983994c87626631b0d8c7f4bec7 | |
| parent | cc252ce781bd00c06a5d422ce5192d73aafed4f1 (diff) | |
[musicdb] improves performance for querying the album view
| -rw-r--r-- | xbmc/music/MusicDatabase.cpp | 33 |
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 { |