1

Consider three entities - Forum, Thread & Topic, with the following relationships

  1. Forum has many Threads
  2. 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?

Assumptions:

  • 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?

Improve this question

0

Reset to default

Your Answer

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