3

I have posts table:

id (int) index
uid (int)
text (text)
created (int)
is_deleted (tinyint)

and a table to store post stats:

post_id (int) index
comments (int)
shares (int)
views (int)

Now when I fetch post, most of the time I also fetch the stats. The reason I created another table was because these stats update frequently specially views. I update these columns in transaction (comments, shares, views) so rows get locked and I wanted to keep the posts table fast to read so I created another table for stats.

But now I think if I merge them in 1 table, it will save me an extra join which I have to make almost 80% of the time.

Question 1: Should I merge the table and keep both of them in 1 table?

Question 2: Updating in a transaction really locks row and prevents fetching it? or does it fetch the last time updated row? (bit confused about it)

1 Answer 1

3

Question 2: Updating in a transaction really locks row and prevents fetching it? or does it fetch the last time updated row? (bit confused about it)

That's not quite what a transaction is.

Any proper ACID database will ensure that when you start updating you don't get overwritten with another update halfway through. This doesn't require that fetching be prevented. It requires that fetching wait until the transaction has succeeded or failed. This way if I change my full name you can't possibly end up pairing my new first name with my old last name with a poorly timed fetch.

Question 1: Should I merge the table and keep both of them in 1 table?

You can simplify the SQL by doing this. The alternative is to leave it as a 1 to 1 relationship between the tables. There is no logic reason to have 1 to 1 relationships but there are performance and, more importantly, conceptual reasons. See Is there ever a time where using a database 1:1 relationship makes sense?

1
  • So basically as I said keep 1 table to read, and another for frequent updates is actually a valid logical reason to keep them separated. Thanks for the link, it has too much information.
    – Shaharyar
    Commented Jun 20, 2017 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.