I never use this feature, so I haven't noticed this phenomena before:
With Settings->Music Library->Compilations->Group compilation albums
together ... I find that my SBC can't browse into many of my genres.
Looking at the mysql slow query log, it looks like a simple attempt to
browse into such a genre triggers FOUR queries that take more than 40
seconds to execute each, and each examines over 4 MILLION rows:
Code:
--------------------
/usr/libexec/mysqld, Version: 5.0.51a-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 080924 15:46:46
# [EMAIL PROTECTED]: squeezecenter[squeezecenter] @ localhost [127.0.0.1]
# Query_time: 43 Lock_time: 0 Rows_sent: 1 Rows_examined: 4622177
SELECT COUNT( DISTINCT( me.id ) ) FROM contributors me LEFT JOIN
contributor_track contributorTracks ON ( contributorTracks.contributor = me.id
) JOIN tracks track ON ( track.id = contributorTracks.track ) LEFT JOIN
genre_track genreTracks ON ( genreTracks.track = track.id ) LEFT JOIN
contributor_album contributorAlbums ON ( contributorAlbums.contributor = me.id
) JOIN albums album ON ( album.id = contributorAlbums.album ) WHERE ( ( ( ( (
album.compilation IS NULL ) OR ( album.compilation = '0' ) ) AND
contributorTracks.role IN ( '1', '5' ) AND genreTracks.genre = '7' ) ) );
# Time: 080924 15:47:32
# [EMAIL PROTECTED]: squeezecenter[squeezecenter] @ localhost [127.0.0.1]
# Query_time: 46 Lock_time: 0 Rows_sent: 24 Rows_examined: 4622225
SELECT me.id, me.name, me.namesort, me.musicmagic_mixable, me.namesearch,
me.musicbrainz_id FROM contributors me LEFT JOIN contributor_track
contributorTracks ON ( contributorTracks.contributor = me.id ) JOIN tracks
track ON ( track.id = contributorTracks.track ) LEFT JOIN genre_track
genreTracks ON ( genreTracks.track = track.id ) LEFT JOIN contributor_album
contributorAlbums ON ( contributorAlbums.contributor = me.id ) JOIN albums
album ON ( album.id = contributorAlbums.album ) WHERE ( ( ( ( (
album.compilation IS NULL ) OR ( album.compilation = '0' ) ) AND
contributorTracks.role IN ( '1', '5' ) AND genreTracks.genre = '7' ) ) ) GROUP
BY me.id ORDER BY me.namesort LIMIT 24;
# Time: 080924 15:48:15
# [EMAIL PROTECTED]: squeezecenter[squeezecenter] @ localhost [127.0.0.1]
# Query_time: 43 Lock_time: 0 Rows_sent: 1 Rows_examined: 4622177
SELECT COUNT( DISTINCT( me.id ) ) FROM contributors me LEFT JOIN
contributor_track contributorTracks ON ( contributorTracks.contributor = me.id
) JOIN tracks track ON ( track.id = contributorTracks.track ) LEFT JOIN
genre_track genreTracks ON ( genreTracks.track = track.id ) LEFT JOIN
contributor_album contributorAlbums ON ( contributorAlbums.contributor = me.id
) JOIN albums album ON ( album.id = contributorAlbums.album ) WHERE ( ( ( ( (
album.compilation IS NULL ) OR ( album.compilation = '0' ) ) AND
contributorTracks.role IN ( '1', '5' ) AND genreTracks.genre = '7' ) ) );
# Time: 080924 15:49:01
# [EMAIL PROTECTED]: squeezecenter[squeezecenter] @ localhost [127.0.0.1]
# Query_time: 46 Lock_time: 0 Rows_sent: 24 Rows_examined: 4622225
SELECT me.id, me.name, me.namesort, me.musicmagic_mixable, me.namesearch,
me.musicbrainz_id FROM contributors me LEFT JOIN contributor_track
contributorTracks ON ( contributorTracks.contributor = me.id ) JOIN tracks
track ON ( track.id = contributorTracks.track ) LEFT JOIN genre_track
genreTracks ON ( genreTracks.track = track.id ) LEFT JOIN contributor_album
contributorAlbums ON ( contributorAlbums.contributor = me.id ) JOIN albums
album ON ( album.id = contributorAlbums.album ) WHERE ( ( ( ( (
album.compilation IS NULL ) OR ( album.compilation = '0' ) ) AND
contributorTracks.role IN ( '1', '5' ) AND genreTracks.genre = '7' ) ) ) GROUP
BY me.id ORDER BY me.namesort LIMIT 24;
--------------------
Those queries take a total of 178 seconds to execute. Of course, the
SBC gives up long before these queries return and needs to be
power-cycled before it can connect to SC again.
My friend Gary up in Montreal noticed this first and brought it to my
attention.
One thing that my be unique about our libraries is that we have a fair
number of cuesheet embedded flacs which are not only multi-artist, but
multi-genre too. I haven't verified yet if this is the determining
factor, though.
I am NOT seeing this when browsing using the web UI.
Does anyone know if this is a known bug?
System details:
SqueezeCenter Version: 7.2.1 - TRUNK @ UNKNOWN - Red Hat - EN - utf8
Perl Version: 5.10.0 i386-linux-thread-multi
MySQL Version: 5.0.67-community-log
Platform Architecture: i686-linux
Total Players Recognized: 1
--
gharris999
------------------------------------------------------------------------
gharris999's Profile: http://forums.slimdevices.com/member.php?userid=115
View this thread: http://forums.slimdevices.com/showthread.php?t=53033
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/lists/listinfo/beta
