title: High-performance multi-tier tag filtering
body: <p>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.</p>
  
  <p>I am trying to accomplish two tasks, and I'm having a very hard time getting the queries to perform acceptably.</p>
  
  <p>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)</p>
  
  <p>Variation: The results are also presentable by artist or by album rather than by track</p>
  
  <p>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.</p>
  
  <p><strong>What I am after is some general guidance in approach</strong>. 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: <a href="http://www.yachtworld.com/core/listing/advancedSearch.jsp" rel="nofollow">http://www.yachtworld.com/core/listing/advancedSearch.jsp</a>, <strong>except</strong> they only have one tier of tags, I am dealing with three.</p>
  
  <p>Table structures:</p>
  
  <pre><code>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)      | 
  
  Table: track_attribute_tags
     Column   |          Type               |   
  ------------+-----------------------------+
   track_id   | integer                     |
   tag_id     | integer                     | 
  
  Table: artist_attribute_tags
     Column   |          Type               |   
  ------------+-----------------------------+
   artist_id  | integer                     |
   tag_id     | integer                     | 
  
  Table: album_attribute_tags
     Column   |          Type               |   
  ------------+-----------------------------+
   album_id   | integer                     |
   tag_id     | integer                     | 
  
  Table: artists
     Column   |          Type               |   
  ------------+-----------------------------+
   id         | integer                     |
   name       | varchar(350)                | 
  
  Table: albums
     Column   |          Type               |   
  ------------+-----------------------------+
   id         | integer                     |
   artist_id  | integer                     | 
   name       | varchar(300)                | 
  
  Table: tracks
     Column    |          Type               |   
  -------------+-----------------------------+
   id          | integer                     |
   artist_id   | integer                     | 
   album_id    | integer                     | 
   compilation | boolean                     | 
   name        | varchar(300)                | 
  </code></pre>
  
  <p><strong>EDIT</strong> I am using PHP, and I am not opposed to doing any sorting or other hijinx in script, my #1 concern is speed of return.</p>