>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.
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.
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?
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/beta
