I have posts table:id (int) indexuid (int)text (text)created (int)is_deleted (tinyint)and a table to store post stats:post_id (int) indexcomments (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)
Post and Stats - Should normalization or keep it together?
database design;performance;transaction;normalization
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?