Listing tracks of an album is slow on big libraries

Which plugin is the bug/enhancement related to ?
Custom Browse

What version of SlimServer/SqueezeCeneter are you using? On what operating
system?
LMS 7.7.3 on Ubuntu 13.10
and
LMS 7.7.2 on Synology NAS (installed via its Package System)

Could you describe the problem ?
Accessing the listing of the tracks of an album via the normal Squeezebox menu 
is quite fast. Accessing the listing via the Custom Browse Plugin is slow.

My SQLite library.db size is about 90MB. And the UI hangs there for 15-30 
seconds.

The logs say:

==========================================================
[14-01-07 13:44:20.1545] 
Plugins::CustomBrowse::MenuHandler::SQLHandler::getData (50) Preparing SQL:
                                                select tracks.id,tracks.title from tracks,contributor_track,albums
                                                where
                                                        tracks.audio=1 and
                                                        contributor_track.track=tracks.id and
                                                        tracks.album={album} and
                                                        contributor_track.contributor={artist} and
                                                        contributor_track.role in (1,5)
                                                group by tracks.id
                                                order by tracks.disc,tracks.tracknum asc,tracks.titlesort asc

[14-01-07 13:44:20.1551] 
Plugins::CustomBrowse::MenuHandler::SQLHandler::_execute (72) Executing: select 
tracks.id,tracks.title from tracks,contributor_track,albums
                                                where
                                                        tracks.audio=1 and
                                                        contributor_track.track=tracks.id and
                                                        tracks.album=2 and
                                                        contributor_track.contributor=3 and
                                                        contributor_track.role in (1,5)
                                                group by tracks.id
                                                order by tracks.disc,tracks.tracknum asc,tracks.titlesort asc
[14-01-07 13:44:40.1553] 
Plugins::CustomBrowse::MenuHandler::SQLHandler::_execute (79) Executing and 
collecting: select tracks.id,tracks.title from tracks,contributor_track,albums
                                                where
                                                        tracks.audio=1 and
                                                        contributor_track.track=tracks.id and
                                                        tracks.album=2 and
                                                        contributor_track.contributor=3 and
                                                        contributor_track.role in (1,5)
                                                group by tracks.id
                                                order by tracks.disc,tracks.tracknum asc,tracks.titlesort asc
==========================================================

The problem with the query is, that the the select from "albums" is not 
necessary. If you do such a query SQLite does a sequential scan through the 
whole albums tables which take a long time. See the output of "explain query 
plan" of this query. The problem is the "SCAN TABLE albums".

==========================================================
$ sudo sqlite3 library.db 
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> explain query plan select tracks.id,tracks.title from 
tracks,contributor_track,albums where tracks.audio=1 and 
contributor_track.track=tracks.id and tracks.album=2 and 
contributor_track.contributor=3 and contributor_track.role in (1,5) group by 
tracks.id order by tracks.disc,tracks.tracknum asc,tracks.titlesort asc;
0|0|2|SCAN TABLE albums USING COVERING INDEX albumsArtworkIndex (~2 rows)
0|1|1|SEARCH TABLE contributor_track USING INDEX contributor_trackContribIndex 
(contributor=?) (~2 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|2|0|SEARCH TABLE tracks USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY
==========================================================

Doing the query without the "from albums" gives the same result, but is much 
faster.

What steps will reproduce the problem?
0. Enable Debug logging of Custom Browse
1. Browse to the Custom Browse menu
2. Select an artist
3. Select an album
4. Have a look in the logs
Original issue reported on code.google.com by michael....@gmail.com on 7 Jan 2014 at 1:47
jbuski / erlandplugins

Listing tracks of an album is slow on big libraries #129
