Question 173132 in Mysql Question posted in
The official MySQL documentation can be found here.

Should I move [total hit] column to separate table in MySQL?

I’m using MySQL and have a table with name Music that has tracks information like title,duration and …
This table also has "total_hit" column that it’s value will be updated frequently by each track played(+1) :

id title duration … total_hit
1 track 1 211 … 1235
2 track 2 302 … 401
3 track 3 180 … 500

My question is :

From performance view, Should I split Music table and add a new one like below?

track_id total_hit
1 1235
2 401
3 500

If I do that :
each time I want to show music information, I have to Join these two tables. In other hand music table will be free from frequently updates.

And if I don’t:
I don’t need to join these two tables, but Music table will be updated many times and many unnecessary locks maybe occurred.

So which one is better?

Tags:

2

Answers


  1. 0

    Of these options, choose one table. It’s simpler and databases are pretty good at handling concurrent updates.

    Set the isolation level to "read uncommitted" so reads won’t be blocked by updates.

    The two table approach has zero benefit: It has the same update performance problem; you have just moved it.

    Login or Signup to reply.
  2. 0

    If you are incrementing some row in the table only 10 times per second, there is no performance problem.

    If > 100 times per second, then, yes, have the counter in a separate table. And that table would have the same PRIMARY KEY as the main table (except for AUTO_INCREMENT).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search