title: Best way to store hashtags and the uses / combinations in a database
body: <p>So there is a huge collection of hashtags ( > 100.000 ) in a database.</p>
  
  <p>Other functionality requires that the hashtags are stored in different tables ordered by the first letter ( tags_a, tags_b, ... ), but I think this fact can be ignored and be handled as one table for the solutions.</p>
  
  <p>I now want to implement a counter for the uses of the tags.</p>
  
  <p>So I think I could just apparent a column to these tables and have the corresponding value counted up each time the tag is used.</p>
  
  <p>That way I would have to join the tables to e.g. get the top 100 used tags...</p>
  
  <p>Is there a more clever and more performant and space saving way to save the counts ?</p>
  
  <p>Also my other need is to store information about the combinations of tags that where used.</p>
  
  <p>For example a user is using tags '#a, #e, #k, #w' and I want to have the information how often  #a was used together with #e &amp; #a with #k &amp; #e with #w and so on...</p>
  
  <p>The first approach that comes in my mind would be a table with columns: tag 1 (FK), tag 2 (FK) and count, but this would be a table that is tag-count * tag-count long... isn't there a better way ?</p>
  
  <p>In the future I would like to have a kind of recommendation like:</p>
  
  <ul>
  <li>You have used #e, you may also want to use #k.</li>
  </ul>
  
  <p>( where #k is one of the most popular combination with #e )</p>
  
  <p>Or something like a spam filter, where I check for possible / usual relations between tags.</p>
  
  <p>How can I store this kind of information, again, in the most performant and space saving way ?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I am expecting up to 1 million 'posts' per day, where each post can have up to 10 tags.</p>
  
  <p>And actually these are not posts, but for simplicity, I will call them so.</p>
  
  <p>The point is, that there will be a kind of AI implemented, that needs to read and learn from these and many other stored data, and also do other stuff, in a relatively short interval of time and we want to minimize the data to handle and process, as much as we can.</p>