title: MySQL - find field with comma separated value by all combinations of a set of numbers
body: <p>We have a product table in MySQL where each row has a field with a comma separated value of options wich combine to become this product. In the scheme below you can see both tables. The field with comma separated values is option_ids, this can for example look like 2,5,6,23 or 4,2,76. These numbers are options wich exist in the option table.</p>
  
  <p>Product:<br>
  <code>|product_id|name|description|option_ids|price|</code></p>
  
  <p>Option:<br>
  <code>|option_id|type_id|label|group|pos|count|price|</code></p>
  
  <p>The input we get is an array of unique options. We wanna find every product that has this set of options or a subset of it in option_ids.</p>
  
  <p>The way we do this now is by generating a powerset of the array of options so that we have all possible combinations of the array of options. Then we generate a mysql query wich queries for all these combinations in 'OR WHERE' statements.</p>
  
  <p>The problem here is that the powerset grows exponentially, so when we have an array of 16 unique options the powerset gives an array of 65536 possibilities. We had to increase the memory usage of php for this to work properly but now we run into MySQL problems because the query gets so enormous.</p>
  
  <p>Is there another way to tackle this problem?</p>
  
  <p>I hope I've given enough information, if not please do ask!</p>
  
  <p>EDIT:
  A third table will get us the same problem because we need to find subsets to... It's not said that an array of options IS ONE product, it can be one product but it can also be 2 or 3 products. For example: options 1,2 make product A, options 2,3 make product B, and options 4,5,6 make product C. If we have option array [1,2,4,5,6] we would want to find product A and C.</p>