title: MySQL and faceted navigation (filter by attributes)
body: <p>I feel like this question has probably been asked a thousand times already, so I apologize if it's been answered. And if so, can someone point me to the right posts/links?</p>
  
  <p>What I'm trying to do is build a faceted navigation for my site. It uses MySQL and here's a rough sketch of the tables I'm using:</p>
  
  <pre>products:
  - id
  - title
  - description
  </pre>
  
  <pre>attributes:
  - product_id
  - name
  - value
  </pre>
  
  <pre>
  categories:
  - id
  - name
  </pre>
  
  <pre>
  products_to_categories:
  - product_id
  - category_id
  </pre>
  
  <p>What I want to do is display a list of available attributes when you are in a category, allowing you to select one or more values for each of those attributes. To give you an example, look at this page from Office Depot: <a href="http://www.officedepot.com/a/browse/binders/N=5+2177/" rel="nofollow noreferrer">http://www.officedepot.com/a/browse/binders/N=5+2177/</a></p>
  
  <p>So far I've used a lot of joins to filter on multiple attributes:</p>
  
  <pre>
  SELECT products.*, a_options.*
  FROM products_to_categories AS pc, products,
  attributes AS a_options,    /* list of attribute/value pairs I can continue to refine on */
  attributes AS a_select1     /* first selected attribute */
  attributes AS a_select2     /* second selected attribute */
  ...
  WHERE pc.category_id = 1
  AND products.id = pc.product_id
  AND a_options.product_id = products.id
  AND a_options.name != 'Color' AND a_options.name != 'Size'
  AND a_select1.product_id = products.id
  AND a_select1.name = 'Color' AND (a_select1.value = 'Blue' OR a_select1.value = 'Black')
  AND a_select2.product_id = products.id
  AND a_select2.name = 'Size' AND a_select2.value = '8.5 x 11'
  </pre>
  
  <p>Basically <code>a_options</code> will return all the attributes for those products that are a subset of the filters I've applied using <code>a_select1</code> and <code>a_select2</code>. So if I use the Binders example from Office Depot, I want to show all available attributes after selecting Blue or Black for Color and "8.5 x 11" for the Size.</p>
  
  <p>I then use PHP code to remove duplicates and arrange the resulting attributes into an array like this:</p>
  
  <pre>
  attributes[name1] = (val1, val2, val3, ...)
  attributes[name2] = (val1, val2, val3, ...)
  </pre>
  
  <p>Is there a way I can speed up my query or write it more efficiently? I have setup indexes on the name and value in the attributes table (and also on all the ID numbers). But if someone selects a couple of attributes, then the query runs slow.</p>
  
  <p>Thanks for your help in advance,<br>
  Sridhar</p>