Consider three entities -
Topic, with the following relationships
- Forum has many Threads
- Forum has many Topics
I want to create a View
forum_details that displays all columns of Forum along with the count of Threads and the count of Topics the forum has.
Question: What is the most performant way to achieve this?
- indexes are created on all foreign keys
- Application is a public wiki/forum (like stackexchange)
- The application is read-heavy. Selects will be 99% of the queries made by end-users
- bulk inserts are not performed by end-users. They might only be needed for development/maintenance work
- Concurrent writes to the table are necessary
- Count estimates are good enough. Exact count will be cherry on top
What I've considered
create view forum_details as select forums.*, coalesce(threads_count, 0) as threads_count, coalesce(topics_count, 0) as topics_count from forums left join (select forum, count(*) as threads_count from threads group by forum) as thc on thc.forum = id left join (select forum, count(*) as topics_count from topics group by forum) as toc on toc.forum = id;
I'm not sure if this is the most performant way of getting these counts
I've also considered storing the counts in the Forum table via triggers, but that doesn't seem to be the brightest idea What are the Cons of using triggers in place of count(*) for counting related entities?