title: Best way to store "tags" for speed in enormous table
body: <p>I'm developing a big content site, with a table "contents", with more than 50 Million of records. Here's the table structure:</p>
  
  <pre><code>contain id(INT11 INDEX), 
  name(varchar150 FULLTEXT), 
  description (text FULLTEXT), 
  date(INT11 INDEX)
  </code></pre>
  
  <p>I wan to add a "tags" to this contents.</p>
  
  <p>I'm think 2 methods:</p>
  
  <ol>
  <li><p>Make a varchar(255 FULLTEXT) "tags" column in table contents. Store all tags separated by comas, and search row by row (Which I think this will be slow) using MATCH &amp; AGAINS.</p></li>
  <li><p>Make 2 tables. First table name "tags" with columns id, tag(varchar(30 INDEX or FULLTEXT?)), "contents_tags" with id, tag_id (int11 INDEX) and content_id (int11 INDEX) and search contents by a JOINS of 3 tables (contents - contents_tags - tags) to retrieve all contents with the tag(s). </p></li>
  </ol>
  
  <blockquote>
    <p>I think this is slow and memory killer because a ENORMOUS JOIN of 50M
    table * contents_tags * tags.</p>
  </blockquote>
  
  <p>What is the best method to store tags to make it as efficient as possible? What is the fastest way to search by a text (for example "movie 3d 2011" and simple tag "video") and to locate contents.?</p>
  
  <p>The size of the table (approx. 5Gb now without tags). The table is a MYISAM because I need to store name and description of the table contents in FULLTEXT to string search (users ca search now by this fields), and need the best speed to search by tags.</p>
  
  <p>Any with experience in this?</p>
  
  <p>Thanks!</p>