title: Big MySQL Tables
body: <p>I'm working on a problem that requires caching paginated "search" results:  <a href="https://stackoverflow.com/questions/347277/paginating-very-large-datasets">Paginating very large datasets</a></p>
  
  <p>The search works as follows:  given an item_id, I find the matching item_ids and their rank.</p>
  
  <p>I'm willing to concede not showing my users any results past, say, 500. After 500, I'm going to assume they're not going to find what they're looking for... the results are sorted in order of match anyway.  So I want to cache these 500 results so I only have to do the heavy lifting of the query once, and users can still page the results (up to 500).</p>
  
  <p>Now, suppose I use an intermediate MySQL table as my cache... that is, I store the top 500 results for each item in a "matches" table, like so: "item_id (INTEGER), matched_item_id (INTEGER), match_rank (REAL)". The search now becomes the extremely fast:</p>
  
  <pre><code>SELECT item.* FROM item, matches 
      WHERE matches.item_id=&lt;item in question&gt;
      AND item.id=matches.matched_item_id 
      ORDER BY match_rank DESC
      LIMIT x,y
  </code></pre>
  
  <p>I'd have no problem reindexing items and their matches into this table as they are requested by clients if the results are older than, say, 24 hours. Problem is, storing 500 results for N items (where N is ~100,000 to 1,000,000) this table becomes rather large... 50,000,000 - 500,000,000 rows.</p>
  
  <p>Can MySQL handle this?  What should I look out for?</p>