StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL and NoSQL: Help me to choose the right one
text
Body
copied!<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 > 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 > 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> show index in thread; PRIMARY id forumid dateline replycount </code></pre> <p>Select explain:</p> <pre><code>mysql> explain SELECT * FROM thread WHERE forumid = 655 AND visible = 1 AND open <> 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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload