Database architecture for review

Question

Database architecture for review

29 views

Good afternoon.

There is a strange dilemma in the implementation of the review on the website.

It is expected that there will be a large number of articles (actually not articles, but that's not the point) and the actual (frequently requested) only a small part of them, with a small period of time actual move these to the archive (without possibility of adding new comments). How to organize storage in a relational database for all the review, taking into account that the main indicator is the speed of processing/delivery?

Actually while thinking about two options:
— General table of comments (confused by the possible speed reduction issue in the growth process of the table)
— A separate table for each article

Allow the possibility that I do not look at that angle on the case.

asked Mar 21, 2019 by invite | 29 views

4 Answers

alexxxst · Answer 1 · 2019-03-23T18:43:38+0000

One table will suffice. Why to fence something? Selection by key (ID of article) is very fast (I for example, in a table with about 4 million records, the sample of a dozen records by key takes about 0.03 seconds).
But a lot of tables (especially if a lot of articles) will quickly clog the cache tables of the database server.
\r
The brakes will be adding reviews to the table, I would recommend (in the case of muscle) to turn on delay_key_write in the table.

alexxxst · Answer 2 · 2019-03-23T18:43:38+0000

And you do not hesitate and try.
One table is a normal variant. Millions of records are not a problem for simple sampling. The main thing is not to kill the stupid requests because the review is likely to be text, as they are in the sample MEMORY are not created. Therefore, you can't be in the query for temporary table was created with them.

bishai · Answer 3 · 2019-03-23T18:43:38+0000

One InnoDB table will easily cope with dozens and even hundreds of millions of records — just need sufficient amount of RAM on the server.
The main thing — not to make it unnecessary for the keys to adding the record wasn't a massive rebuild indexes.
If there is a problem of search of the text of the review, either to look in the direction of full-text indexes MyISAM (which is much worse than running in simultaneous record and reading), or to use specialized solutions like Sphinx.

Database architecture for review

Please log in or register to add a comment.

Please log in or register to answer this question.

4 Answers

Please log in or register to add a comment.

Please log in or register to add a comment.

Please log in or register to add a comment.

Please log in or register to add a comment.

Related questions

Most popular tags