[marantz]

Hello!
When pressing an artist in kodi to view albums, this is the sql query kodi uses.

Code:

SELECT albumview.* FROM albumview 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 WHERE song_artist.idArtist = 1919 OR  album_artist.idArtist = 1919 GROUP BY albumview.idAlbum

3 rows in set (2.70 sec)

I played around a bit, and came to this.

Code:

select album.idAlbum, album.strAlbum, album.strMusicBrainzAlbumID, album.strArtists, album.strGenres, album.iYear, album.strMoods, album.strStyles, album.strThemes, album.strReview, album.strLabel, album.strType, album.strImage, album.iRating, album.bCompilation, MIN(song.iTimesPlayed)
FROM album JOIN song ON song.idAlbum = album.idAlbum JOIN album_artist ON album.idAlbum = album_artist.idAlbum JOIN song_artist ON song.idSong = song_artist.idSong where song_artist.idArtist = 1919 OR album_artist.idArtist = 1919 GROUP BY album.idAlbum;

(1.06 sec)

if you only check album_artist.idArtist with the later queryits down to 0.02s.

So far ive tried about 30 artists I have and get the same results so far, any input? is this useable?

[Kib]

I've been looking at this together with soppman (marantz) in IRC and so far it looks like it is MUCH faster, especially with large libraries.

I put this here to save people some time when he they want to take a look at it:

I've had a search through the code and found https://github.com/xbmc/xbmc/blob/master....cpp#L5761 which lists:

Code:

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");
    }

but the albumview.* part comes from somewhere else and is probably also used for other queries, which makes it a bit tricky.

[Kib]

I also noticed that the albumview is already joined on the song, and grouped by album.idalbum

See https://github.com/xbmc/xbmc/blob/master...e.cpp#L292

Code:

m_pDS->exec("CREATE VIEW albumview AS SELECT "
              "        album.idAlbum AS idAlbum, "
              "        strAlbum, "
              "        strMusicBrainzAlbumID, "
              "        album.strArtists AS strArtists, "
              "        album.strGenres AS strGenres, "
              "        album.iYear AS iYear, "
              "        album.strMoods AS strMoods, "
              "        album.strStyles AS strStyles, "
              "        strThemes, "
              "        strReview, "
              "        strLabel, "
              "        strType, "
              "        album.strImage as strImage, "
              "        iRating, "
              "        bCompilation, "
              "        MIN(song.iTimesPlayed) AS iTimesPlayed, "
              "        strReleaseType "
              "FROM album"
              " LEFT OUTER JOIN song ON"
              "   album.idAlbum=song.idAlbum "
              "GROUP BY album.idAlbum");

[marantz]

using a UNION select

Code:

select album.idAlbum, album.strAlbum, album.strMusicBrainzAlbumID, album.strArtists, album.strGenres, album.iYear, album.strMoods, album.strStyles, album.strThemes,  album.strReview, album.strLabel, album.strType, album.strImage, album.iRating, album.bCompilation, MIN(song.iTimesPlayed)    FROM album JOIN song ON song.idAlbum = album.idAlbum JOIN album_artist ON album.idAlbum = album_artist.idAlbum JOIN song_artist ON song.idSong = song_artist.idSong  where song_artist.idArtist = 2011  UNION select album.idAlbum, album.strAlbum, album.strMusicBrainzAlbumID, album.strArtists, album.strGenres, album.iYear, album.strMoods, album.strStyles, album.strThemes,  album.strReview, album.strLabel, album.strType, album.strImage, album.iRating, album.bCompilation, MIN(song.iTimesPlayed)    FROM album JOIN song ON song.idAlbum = album.idAlbum JOIN album_artist ON album.idAlbum = album_artist.idAlbum JOIN song_artist ON song.idSong = song_artist.idSong  where album_artist.idArtist = 2011  GROUP BY album.idAlbum;

Im down to 0.02s

[marantz]

Seems like views are simply that slow, even without joins etc.
I made a test view called sippan like this:

select `album`.`idAlbum` AS `idAlbum`, `album`.`strAlbum` AS `strAlbum`,`album`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,`album`.`strArtists` AS `strArtists` from album group by `album`.`idAlbum`

No joins, or other stuff. as you can see its still slow.

MySQL [music49]> select * from sippan where idAlbum = 2 ;
+---------+------------------------------------------------------+-----------------------+----------------------+
| idAlbum | strAlbum | strMusicBrainzAlbumID | strArtists |
+---------+------------------------------------------------------+-----------------------+----------------------+
| 2 | People's Instinctive Travels and the Paths of Rhythm | NULL | A Tribe Called Quest |
+---------+------------------------------------------------------+-----------------------+----------------------+
1 row in set (0.48 sec)

MySQL [music49]>
select * from album where idAlbum = 2 ;
<output removed>
1 row in set (0.01 sec)

[marantz]

Tried with sqlite, works fine.

rpi2:

Run Time: real 1.717 user 1.406408 sys 0.306259 kodi default
Run Time: real 0.001 user 0.001234 sys 0.000294 no view

rpi1:
CPU Time: user 9.710000 sys 0.940000 kodi query
CPU Time: user 0.010000 sys 0.000000 no view

maybe the db will fly even on raspberrys if no views would be used.

[marantz]

the sql need to happen within the view I guess

[marantz]

perhaps its possible to create a view with this:

Code:

select album.idAlbum, album.strAlbum, album.strMusicBrainzAlbumID, album.strArtists, album.strGenres, album.iYear, album.strMoods, album.strStyles, album.strThemes,  album.strReview, album.strLabel, album.strType, album.strImage, album.iRating, album.bCompilation, MIN(song.iTimesPlayed)    FROM album JOIN song ON song.idAlbum = album.idAlbum JOIN album_artist ON album.idAlbum = album_artist.idAlbum JOIN song_artist ON song.idSong = song_artist.idSong    UNION select album.idAlbum, album.strAlbum, album.strMusicBrainzAlbumID, album.strArtists, album.strGenres, album.iYear, album.strMoods, album.strStyles, album.strThemes,  album.strReview, album.strLabel, album.strType, album.strImage, album.iRating, album.bCompilation, MIN(song.iTimesPlayed)    FROM album JOIN song ON song.idAlbum = album.idAlbum JOIN album_artist ON album.idAlbum = album_artist.idAlbum JOIN song_artist ON song.idSong = song_artist.idSong    GROUP BY album.idAlbum

and let kodi insert where album_artist.idArtist = % and where song_artist.idArtist = % into the view when searching
if nothing is inserted it shows all like it does now.

edit: that would suck

[xhaggi]

fixed with https://github.com/xbmc/xbmc/pull/6941

[marantz]

fixed with https://github.com/xbmc/xbmc/pull/6941

here is another slow SELECT * FROM season_view WHERE season_view.idShow = 45 if you feel bored.

it also got a group by in the view. thanks for the last fix btw!!