title: Optimize SQL query across list-of-items
body: <p>I have about 400K product rows in a database. All queries on this dataset are optimized to return in under 0.4s and run well. However, I need to add an additional field called Territories, that could include anywhere from 0-250 territories (any country in the world), usually around 150. Of course the 'relational' way to add this would be to add a territory with an FKey to the product table, yet when I try that with a join, I usually get queries in the 2-7s range, as now I'm querying about 60M rows, and the query is something like:</p>
  
  <pre><code>SELECT product from table join ... 
  WHERE territories in ['USA', 'France', 'Germany']
  ORDER BY some_field LIMIT 50
  </code></pre>
  
  <p>This is way too slow for the application requirements, and I was wondering how I might be able to speed up this query using some sort of potentially flat field. Here were some ideas I had:</p>
  
  <ul>
  <li>Store it as a json array (tried this, performance was even worse than a join).</li>
  <li>Store this as a set (don't think possible, as set is <a href="https://dev.mysql.com/doc/refman/8.0/en/set.html" rel="nofollow noreferrer">8 bytes</a> so less than 100 values).</li>
  <li>Do something like full-text search field and index each country as a single unit (might be ok, but very poor having to run the sort at the end)</li>
  <li>Use four BIGINTs with a bitmap across those four fields for the 240 values (seems like a terrible idea, but an idea I had nonetheless).</li>
  </ul>
  
  <p>My requirement here is the query needs to return in under 0.5s. What might be the best way to do this?</p>