Comment database architecture

Question

Database

Good afternoon.

There was a strange dilemma in the implementation of comments on the site.

It is assumed that there will be a large number of articles (actually not articles, but that’s not the point), and only a small part of them are relevant (frequently requested), with a short period of time these relevant ones go to the archive (without the possibility of adding new comments) and appear new. What is the best way to organize storage in a relational database for all comments, given that the main indicator is the speed of processing / issuing?

Actually, while thoughts are around two options:
- General table of comments (confuses the possible decrease in the speed of issuance in the process of table growth)
- A separate table for each article

I admit the possibility that I look at this matter from the wrong angle.

Answer 1

One table is enough. Why bother with something? Sampling by key (article ID) is very fast (for example, in my table there are about 4 million records, fetching a dozen records by key takes about 0.03 seconds).
But a bunch of tables (especially if there are a lot of articles) will quickly clog the database server table cache.
The most brakes will be when adding comments to the table, I would recommend (in the case of a muscle) to include delay_key_write in the table.

Answer 2

Definitely one table.

Answer 3

Don't be embarrassed and try it.
One table is quite normal. Millions of records are not a problem for a simple sample. The main thing is not to kill with stupid requests, since the comment will most likely be text, and they are not created when fetching in MEMORY. Therefore it is impossible that in request temporary on the table with them was created.

Answer 4

One InnoDB table can easily cope with several tens and even hundreds of millions of records - you just need enough RAM on the server.
The main thing is not to make extra keys in it, so that when adding a record there is no large-scale rebuilding of indexes.
If there is a task of searching in the text of comments, then either look towards full-text MyISAM indexes (which works much worse when writing and reading at the same time), or use specialized solutions like Sphinx.