title: Multi-dimensional search on a table
body: <p>I have the following table that stores the preferences of the users in the system</p>
  
  <pre><code>UserId | Product   | Brand     | City      |
  -------------------------------------------
  A      | Soap      | Tide      | NYC       |
  A      | Cereal    | Dont-care | NYC       |
  B      | Dont-Care | Tide      | Dont-care |
  C      | Shampoo   | Dont-care | Dont-Care |
  </code></pre>
  
  <p>I would like to search this based on user provided search values. So if one searches for</p>
  
  <pre><code>City: NYC, Brand: Tide
  </code></pre>
  
  <p>the output should be:</p>
  
  <pre><code>    A    | Soap      | Tide      | NYC       |
      B    | Dont-Care | Tide      | Dont-care |
  </code></pre>
  
  <p>where as if they search for</p>
  
  <pre><code>Brand: Tide, Product: Soap
  </code></pre>
  
  <p>the result should be: </p>
  
  <pre><code>    A    | Soap      | Tide      | NYC       |
  </code></pre>
  
  <p>The current solution I have, is the following query (where null represents 'don't care') going against a MySQL table:</p>
  
  <pre><code>select *
  from user_preferences
  where (product is null or product = &lt;user provided value&gt;)
  and (brand is null or brand = &lt;user provided value&gt;)
  and (city is null or city = &lt;user provided value&gt;)
  </code></pre>
  
  <p>Though it works as expected, the [and + (or)] combination makes me think this is not the right way to do this. I am also fairly certain that once the dataset increases, the query will not perform well. </p>
  
  <p>What would be most efficient way of storing and retrieving such data? Are there any no-sql type approaches that can be used to make this efficient?</p>
  
  <p><strong>Update</strong> </p>
  
  <p>After some googling around I figured the approach I have may be the safest bet. One factor I still am ambivalent about with this approach is that adding another 'searchable' attribute would mean adding a new column.</p>
  
  <p>This blog about the <a href="http://karwin.blogspot.com/2009/05/eav-fail.html" rel="nofollow">EAV anti-pattern</a> provides some good reading material on such a scheme. Also see how <a href="http://bret.appspot.com/entry/how-friendfeed-uses-mysql" rel="nofollow">friend-feed uses MySQL</a> for another take on storing variable attributes in a table.</p>