On Feb 1, 2010, at 8:19 PM, Phil Meyer wrote: >> The way it works now is that this query is run after every track is added, >> when there is not an explicit COMPILATION tag present. So it gets run once >> per track. The results from the query are longer for each subsequent track >> in a given album, but unless you have an album with a ton of tracks that's >> not a big deal. I benchmarked this today and it's actually not that slow, >> so maybe the drastic slowdown came from somewhere else. I need to do some >> more testing. >> > My initial thoughts were pretty much the same as Erland's. I tried adjusting > the query to use a join, and it appeared to be slightly faster in MySQL: > > SELECT contributor, track > FROM contributor_track AS ct > JOIN tracks AS t ON t.id = ct.track AND t.album = ? > WHERE ct.role = ? > > I wondered if the ORDER BY was really needed? I was also going to check the > indexes in MySQL to see if there was soemthing that could be done.
I guess not, we could do the sorting in Perl after the query runs. > But the real problem is executing the query many times - once per track on > each album. That must be a big overhead. > > So, how about doing it all in one query: > > SELECT album_id > FROM > ( > SELECT album_id > FROM > ( > SELECT a.id AS album_id, ct.contributor, count(t.id) AS track_count > FROM albums AS a > JOIN tracks AS t ON t.album = a.id > JOIN contributor_track AS ct ON t.id = ct.track AND role = 1 > WHERE a.compilation IS NULL > AND (SELECT COUNT(contributor) FROM contributor_album WHERE album = a.id > AND role = 1) > 1 > GROUP BY a.id, ct.contributor > ) AS a > GROUP BY album_id, track_count > ) AS a > GROUP BY album_id > HAVING COUNT(*) > 1 > > That runs in a few milliseconds, and should run on both MySQL and Sqlite. Thanks, I'll take a closer look at this query later. The reason it's done per-track now is so the code can be the same for in-process and external scanning. In-process does not have phases, so there is no "merge" phase, everything runs at track time to ensure a consistent state after each track. > Then turn that into an UPDATE query that sets COMPILATION=1. > > I've used role=1 to only look for artist contributors. This could be IN (1, > 2, 3, 4) to include composers, conductors and band/orchestra. But I wonder > how the scanner currently considers these different contributor roles - does > it matter if different tracks have the same set of contributors but different > contributor roles? i.e. if Mozart exists on some tracks as composer and on > other tracks as an artist role, but effectively appears as a contributor once > on each track on an album? The merge VA code has always only used role=1, it does not care about the other types of artists or any prefs. Your Mozart case would be considered a compilation, and you'd want to fix the tags in that case anyway. _______________________________________________ beta mailing list [email protected] http://lists.slimdevices.com/mailman/listinfo/beta
