Here are optimizations of those two queries as executed in MySQL Query
Browser:
Getting the count of artists in the genre:
Code:
--------------------
SELECT Count( DISTINCT( contributors.id))
FROM ((contributors INNER JOIN contributor_track ON contributors.id =
contributor_track.contributor) INNER JOIN tracks ON contributor_track.track =
tracks.id) INNER JOIN genre_track ON tracks.id = genre_track.track
WHERE (((contributor_track.role)=1) AND ((genre_track.genre)=7));
--------------------
This returns the value "37" in almost no time at all: 1 row fetched in
0.0005s (0.820s)
Getting the actual artist list:
Code:
--------------------
SELECT DISTINCT contributors.name, contributors.namesort
FROM ((contributors INNER JOIN contributor_track ON
contributors.id=contributor_track.contributor) INNER JOIN tracks ON
contributor_track.track=tracks.id) INNER JOIN genre_track ON
tracks.id=genre_track.track
WHERE (((contributor_track.role)=1) AND ((genre_track.genre)=7))
ORDER BY contributors.namesort;
--------------------
This returns the sorted list of artists: 37 rows fetched in 0.0006s
(0.2074s)
So, one can see that the needed data can be fetched via MUCH more
efficient queries.
Too bad I don't have a friggin clue as to how to express that in DBI.
--
gharris999
------------------------------------------------------------------------
gharris999's Profile: http://forums.slimdevices.com/member.php?userid=115
View this thread: http://forums.slimdevices.com/showthread.php?t=46850
_______________________________________________
jive mailing list
[email protected]
http://lists.slimdevices.com/cgi-bin/mailman/listinfo/jive
