Is it possible to store information about the number of referring entities in an entity table?

Question

MySQL

For example, there is a table with goods. There are users who can write their reviews on these products. Suppose that the number of users grows every day by 100..200 pieces and they leave more and more reviews of 500..10000 per day.
So, if earlier we pulled out the number of reviews for a product from the reviews table (i.e. one request with a choice of a product, another one with the number of reviews for it), now there is an idea to make just an additional. a column for a product with the name "qty_reviews" of the integer type and update the counter there after each addition or removal of a review (a review was added to the product, qty_reviews was increased). For example the table will look like this
--------------------------------
| id | name | qty_reviews |
--------------------------------
The example is a little abstract, the example with numbers I want to emphasize the high load of the project (i.e. such loads are planned). The database is now used by mysql (in the future there will be more Cassandra)
Is it possible to create such columns for a product that will be constantly updated?
I have a suspicion that such requests can either kill the database or create a collision when writing / issuing

Answer 1

Well, in order not to kill the base and not create collisions, there is an instruction WITH NOLOCK
Regarding denormalization, I can suggest you make a ReviewsCounts table where to store the product id and the number of reviews. Accordingly, you will not load the table of the goods themselves with triggers. You can make a pair key product id/review number and number of reviews by grouping product id and MAX(review number).

Answer 2

Is it not enough just to add an index to the product ID field?
As far as I understand, if it is available, the request for the number of rows with such an index practically does not bother the database.
Or I'm wrong?

Answer 3

I have a suspicion that such requests can either kill the database or create a collision when writing / issuing

MySQL: https://dev.mysql.com/doc/refman/5.0/en/set-transa...
But with Cassandra it will be more difficult: www.datastax.com/dev/blog/row-level-isolation