Erland
After using the Custom Scan, SQL Playlist and Dynamic Playlist plugins
for a while and now being able to generate playlists using customised
attributes I thought I'd turn my attention to trying to diagnose
underlying issues that may contribute to dropouts when defining and/ or
editing playlists.
According to slimserver my library comprises 5,125 albums, 76,769 songs
& 1868 artists. Running
Code:
--------------------
select module,attr,count(*) from customscan_track_attributes group by
module,attr;
--------------------
returns:
module attr count(*)
customtag ALBUM RATING 64253
customtag MOOD 705222
customtag STYLE 179554
customtag THEME 16063
mixedtag ALBUM 76769
mixedtag ARTIST 77527
mixedtag BAND 13
mixedtag COMPOSER 100277
mixedtag GENRE 76769
mixedtag YEAR 76769
According to SQLyog the customscan_track_attributes table currently
contains 1,473,532 records.
The reason the record count is so high is because in the current table
design each track in an album can have multiple entries assigned within
each of the attributes, eg any given track will have as many records to
describe the MOOD attribute as there are comma separated entries in the
MOOD tag within the associated FLAC file. As an aside I should mention
though that a lot of this information is in my case redundant in that
the tag attributes are really album based rather than track based,
albeit they are stored in the tags of every track belonging to that
album - every track that belongs to an album will have identical MOOD,
STYLE,THEME & ALBUM RATING tags. In the strictest sense therefore,
when I use your plugins to produce dynamic tag based playlists what I
am really doing is instructing slimserver to *select songs from albums*
that meet the genre, mood, style & album ratings I specify.
I collected some stats re my test and production libraries that may be
of interest. In my test library all albums/tracks have some
combination of STYLE, MOOD, THEME & ALBUM RATING tags. My production
library is unfortunately not quite as well organised (yet). In my test
system customscan_track_attributes contains 230,754 records for 390
albums/6209 songs/236 artists. My production
customscan_track_attributes contains 1,473,532 records for 5,125
albums. Extrapolating the test library stats means around 2/3 of my
production albums don't currently contain mood, style, theme or album
rating tags - if they did the record count would be closer to >=3m,
which I'm guessing would kill slimserver if a complex query was
invoked. If I'm not mistaken this also explains (at least partially)
the dropouts I'm currently experiencing when defining and/ or editing
playlists - in my case slimserver is having to filter across > 1M
records to build the playlisting listboxes and also having to do the
same when generating a playlist.
Considering the above, I see two potential fixes, albeit they are quite
different in implication:
*Approach 1* - given attributes are album based, store the attributes
for the albums - all things being equal this in itself should
significantly reduce the number of attributes records in the ratio of
albums/tracks in the library, but would require the playlisting select
statements to link tracks to albums. The key drawback here is that this
would negatively impact anybody wanting to use the plugins to do
track-based attribute playlisting where they have actually assigned
attributes at the track level rather than at the album level as I have
done.
*Approach 2* - create an attributes table for each of the attributes a
user defines in the Custom Scan configuration and link these to one
another, and also to a series of track/ attributes tables. This
approach should allow playlists creation/ editing to be done very
efficiently by eliminating the need to filter records from a massive
customscan_track_attributes table in order to populate listboxes and/or
populate playlists. If my logic serves correctly, the playlist
creation/ editing would then only require the listboxes to populate
records from a much smaller series of tables, in my case MOOD, STYLE,
THEME & ALBUM RATING.
I've also given some thought to eliminating/ reducing the need to do a
complete rescan for Custom and Mixed Tag scanning when adding new
albums or modifying tags for files already in the library:
- Eliminating the need for a full rescan can possibly be done by
storing the last album.id and track.id's which are both auto increment
fields and then use the data in the albums and tracks tables to
determine which files need to be subjected to the custom and mixed tag
scanning routines and thus *added* to the customscan_track_attributes
table.
- Getting around the need to do a full rescan to pick up changed tags
on existing albums/tracks could perhaps be done indirectly by offering
the possibility of "Rescan tags for this album / these albums" in a
browse or perhaps even the albums loaded by a user to the current
playlist.
- a more integrated approach, as you have pointed out, may be to to
remember the newest "file modification time" when scanning and then
only scanning records whose modification time in the tracks table is
later than that last stored.
Just my 2c, looking forward to comments/ suggestions from others.
--
egd
Thecus N5200PRO >> Transporter >> ATC SCA2 >> ATC SCM100SLAT
------------------------------------------------------------------------
egd's Profile: http://forums.slimdevices.com/member.php?userid=3425
View this thread: http://forums.slimdevices.com/showthread.php?t=38714
_______________________________________________
plugins mailing list
[email protected]
http://lists.slimdevices.com/lists/listinfo/plugins
