[#MBS-4935] count.recording.Nreleases statistics are calculated incorrectly and inefficiently - MusicBrainz JIRA

MusicBrainz Server

count.recording.Nreleases statistics are calculated incorrectly and inefficiently

Created: 26/Jun/12 07:48 AM Updated: 09/Jul/12 07:31 AM Resolved: 09/Jul/12 07:31 AM

Component/s:

Misc features

Affects Version/s:

None

Fix Version/s:

Bug fixes, 2012-07-09

Description

« Hide

Current code uses:

SELECT r.id, count(*) AS c FROM recording r JOIN track t ON t.recording = r.id JOIN tracklist tl ON tl.id = t.tracklist JOIN medium m ON tl.id = m.tracklist GROUP BY r.id 
UNION 
SELECT r.id, 0 as c FROM recording r LEFT JOIN track t ON t.recording = r.id WHERE t.id IS NULL

This counts the number of mediums that a recording appears on, and also doesn't check for distinct mediums. This ends up being wrong for e.g. http://musicbrainz.org/recording/b6293447-05bf-4123-bfe5-099754348bbf, which appears on http://musicbrainz.org/release/b7fadf15-8121-4f46-b10d-a33b6ec35858 as both track 1 and track 7, or any release where a recording appears on two different mediums within the same release.

Also, the UNION there is more inefficient than it needs to be.