title: MySQL and NoSQL: Help me to choose the right one
body: <p>There is a big database, 1,000,000,000 rows, called threads (these threads actually exist, I'm not making things harder just because of I enjoy it). Threads has only a few stuff in it, to make things faster: (int id, string hash, int replycount, int dateline (timestamp), int forumid, string title)</p>
  
  <p>Query:</p>
  
  <p><code>select * from thread where forumid = 100 and replycount &gt; 1 order by dateline desc limit 10000, 100</code></p>
  
  <p>Since that there are 1G of records it's quite a slow query. So I thought, let's split this 1G of records in as many tables as many forums(category) I have! That is almost perfect. Having many tables I have less record to search around and it's really faster. The query now becomes:</p>
  
  <p><code>select * from thread_{forum_id} where replycount &gt; 1 order by dateline desc limit 10000, 100</code></p>
  
  <p>This is really faster with 99% of the forums (category) since that most of those have only a few of topics (100k-1M). However because there are some with about 10M of records, some query are still to slow (0.1/.2 seconds, to much for my app!, <strong><em>I'm already using indexes!</em></strong>).</p>
  
  <p>I don't know how to improve this using MySQL. Is there a way?</p>
  
  <p>For this project I will use 10 Servers (12GB ram, 4x7200rpm hard disk on software raid 10, quad core)</p>
  
  <p>The idea was to simply split the databases among the servers, but with the problem explained above that is still not enought.</p>
  
  <p>If I install cassandra on these 10 servers (by supposing I find the time to make it works as it is supposed to) should I be suppose to have a performance boost?</p>
  
  <p><em><strong>What should I do? Keep working with MySQL with distributed database on multiple machines or build a cassandra cluster?</em></strong></p>
  
  <p>I was asked to post what are the indexes, here they are:</p>
  
  <pre><code>mysql&gt; show index in thread;
  PRIMARY id
  forumid
  dateline
  replycount
  </code></pre>
  
  <p>Select explain:</p>
  
  <pre><code>mysql&gt; explain SELECT * FROM thread WHERE forumid = 655 AND visible = 1 AND open &lt;&gt; 10 ORDER BY dateline ASC LIMIT 268000, 250;
  +----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+
  | id | select_type | table  | type | possible_keys | key     | key_len | ref         | rows   | Extra                       |
  +----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+
  |  1 | SIMPLE      | thread | ref  | forumid       | forumid | 4       | const,const | 221575 | Using where; Using filesort | 
  +----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+
  </code></pre>