title: MySQL: SQL and DB for product with multiple categories
body: <p>I am working on a website which hold millions of records now (apologies cannot reveal which site) initially it had few hundred records so the query below was acceptable</p>
  
  <p>Query: SELECT * FROM….WHERE category LIKE ‘%,3,%’;</p>
  
  <p>But now it just kills the database as for each query it has to go through the entire 2Mil records with above query</p>
  
  <p><strong>Category table</strong><br>
  ID  NAME<br>
  1   Female<br>
  2   Fashion<br>
  3   Clothing<br>
  4   Accessories<br>
  5   Top<br>
  6   Dress<br>
  7   Earring<br>
  8   Short dress<br>
  9   Long dress<br>
  10  Male<br></p>
  
  <p><strong>Product table</strong><br>
  ID…..Category….other bits<br>
  1   ,1,2,3,6,9, ……<br>
  2   ,1,2,4,7,<br>
  3   ,1,2,3,5,<br>
  4   ,10,2,3,4,<br></p>
  
  <p>you have the picture as what is happening above. Now if I do FullText index on category row in product table it gives only 1 cardinality :(</p>
  
  <p>How can I overcome this?
  I have considered duplicating row with each category but the database is huge currently 2 GIG and with duplicates it will turn roughly 10 GIG… more like a problem then a solution</p>