So im working on a project where counts for like total likes or comments would be needed. This is pretty common in most any project. What im trying to figure out is a more expert view on this matter as there are a few methods to do this, based on the situation and kind of app
Method #1
Use a Subquery to COUNT(*) the totals for example
SELECT articles.*, (SELECT COUNT(*) FROM comments WHERE comments.parent_id = page.id) AS comments_count FROM articles
Advantage: accurate, single query
Disadvantage: caching gets more complicated for the index pages and its a little more stressful query.
Method #2 (most common)
Adding a comments_count, likes_count etc column to the database and updating it with a COUNT(*) query.
Advantage: no extra queries, and not stressful.
Disadvantage: Count can go wrong, caching won't be very accurate
Method #3
Each item gets a separate query for counts, forexample on an article index page 20 results are returned with one query and then 20 count queries are sent for each of their comments total. These queries are then cached, so they wont be called on the next page view. Once changed the cache is deleted for just that item and its called again.
Advantage: accurate, easy caching.
Disadvantage: First loads are stressful say a listing of 100 items, would be 101 queries.
What are your opinions on these methods and which have you used most, or are there better solutions ?
I would argue that 2 isn't that common, but that aside, you're just repeating information that already easily exists so there's no need. Method 3 shouldn't be considered an option. 1 is the way to go, every time.
I usually use #1 (and do the count only for the displayed items when using pagination).
#2 can be best when dealing with a lot of trafic.
Never ever try #3. Don't even consider it as an option.
Donate
