sql - MySQL- Counting rows VS Setting up a counter -

i have 2 tables posts<id, user_id, text, votes_counter, created> , votes<id, post_id, user_id, vote>. here table vote can either 1 (upvote) or -1(downvote). if need fetch total votes(upvotes - downvotes) on post, can in 2 ways.

1. use count(*) count number of upvotes , downvotes on post votes table , maths.
2. set counter column votes_counter , increment or decrement everytime user upvotes or downvotes. extract votes_counter.

my question 1 better , under condition. saying condition, mean factors scalability, peaktime et cetera.

to know, if use method 1, table millions of rows, count(*) heavy operation. avoid situation, if use counter during peak time, votes_counter column might deadlocked, many users trying update counter!

is there third way better both , simple implement?

the 2 approaches represent common tradeoff between complexity of implementation , speed.

• the first approach simple implement, because not require additional coding.
• the second approach potentially lot faster, when need count small percentage of items in large table
• the first approach can sped designed indexes. rather searching through whole table, rdbms retrieve few records index, , counts using them

the second approach can become complex quickly:

• you need consider happens counts when user gets deleted
• you should consider happens when table of votes manipulated tools outside program. example, merging records 2 databases may prove lot more complex when current counts stored along individual ones.

i start first approach, , see how performs. try optimizing indexing. finally, consider going second approach, possibly writing triggers update counts automatically.