I have a large database of artists, albums, and tracks. Each of these items may have one or more tags assigned via glue tables (track_attributes, album_attributes, artist_attributes). There are several thousand (or even hundred thousand) tags applicable to each item type.

I am trying to accomplish two tasks, and I'm having a very hard time getting the queries to perform acceptably.

Task 1) Get all tracks that have any given tags (if provided) by artists that have any given tags (if provided) on albums with any given tags (if provided). Any set of tags may not be present (i.e. only a track tag is active, no artist or album tags)

Variation: The results are also presentable by artist or by album rather than by track

Task 2) Get a list of tags that are applied to the results from the previous filter, along with a count of how many tracks have each given tag.

What I am after is some general guidance in approach. I have tried temp tables, inner joins, IN(), all my efforts thus far result in slow responses. A good example of the results I am after can be seen here: http://www.yachtworld.com/core/listing/advancedSearch.jsp, except they only have one tier of tags, I am dealing with three.

Table structures:

Table: attribute_tag_groups
   Column   |          Type               |   
------------+-----------------------------+
 id         | integer                     |
 name       | character varying(255)      | 
 type       | enum (track, album, artist) | 

Table: attribute_tags
   Column                       |          Type               |   
--------------------------------+-----------------------------+
 id                             | integer                     |
 attribute_tag_group_id         | integer                     |
 name                           | character varying(255)  
Edit
Report