>Makes no difference. The database is set up for M:M relationships as it >is (albums : contributor_album : contributor). If every album had an >album artist or -artists- in the database, it would still take just one >query to generate a list of albums with album artists. > I thought you were suggesting that if it always had album artist(s) stored, rather than sometimes needing to fetch artist(s) for tracks, that it would simplift and aid performance. I agree, it would simplify, but performance isn't really impacted (and could be worse, because it would need to store more contributors in the link table?).
There's no real difference if the query has to fetch album artist contributor roles, and/or artist contributor roles. A query to fetch details that would be required to calculate the display of the albums list (sorted by album), would be something like this: SELECT a.id, a.titlesort, c.id, c.namesearch, ca.role, a.compilation FROM contributor_album ca JOIN albums a ON a.id = ca.album JOIN contributors c ON c.id = ca.contributor AND ca.role in (1,5) WHERE c.id=1137 ORDER BY titlesort Of course, the query would be tweaked a bit depending on settings (eg. add role 4 to pull back Band role instead of reporting artist roles). For me, using MySQL DB, this returns the artists to report against all albums in my 4000+ album library in less than 50ms. If fetching for a single artist, it's ~ 1ms. The application can then decide what data to use to render (if compilation, ignore artist roles), rather than overcomplicate the query (or make lots of smaller queries) to do this at SQL level. >I haven't looked at it in a while. At one time it appeared to be doing >a query for every track on every album being listed. It may be smarter >now. There is a contributor_album table, that holds artist roles required for album queries, so it shouldn't need to search through all possible contributor roles mapped to tracks. >Still, there's no reason to be doing dozens of queries to generate >a simple album list that also shows the album artists. I agree. I guess this is the way that the DB access layer code works; if specialised SQL were written, it would undoubtedly be more efficient. But it goes through layers that map tables to objects, abstracting the DB engine. _______________________________________________ beta mailing list [email protected] http://lists.slimdevices.com/mailman/listinfo/beta
