Slow queries in Discourse

Development
9

From Database we find a slow query(1141257 Nanosecond) with unnecessary JOIN and DISTINCT produced by directory_items_controller.rb as follows

SELECT 
  COUNT(DISTINCT "directory_items"."id") 
FROM 
  "directory_items" 
  LEFT OUTER JOIN "users" ON "users"."id" = "directory_items"."user_id" 
  LEFT OUTER JOIN "group_users" ON "group_users"."user_id" = "users"."id" 
  LEFT OUTER JOIN "groups" ON "groups"."id" = "group_users"."group_id" 
  LEFT OUTER JOIN "user_stats" ON "user_stats"."user_id" = "directory_items"."user_id" 
WHERE 
  "directory_items"."period_type" = 1 
  AND "groups"."id" = 2898

It retrieved nothing from user_stats and groups.id = 2898 can be replcaed by group_users.group_id = 2898 which means we can remove table groups and user_stats from query. At the same time, because of UNIQUE (user_id, group_id) in group_users and PRIMARY KEY (id) in users, JOIN won’t create duplicate records, so we can also remove DISTINCT from query as shown below:

SELECT 
  COUNT("directory_items"."id") 
FROM 
  "directory_items" 
  LEFT OUTER JOIN "users" ON "users"."id" = "directory_items"."user_id" 
  LEFT OUTER JOIN "group_users" ON "group_users"."user_id" = "users"."id" 
WHERE 
  "directory_items"."period_type" = 1 
  AND "group_users"."group_id"  = 2898

This opt query take 941700 Nanosecond(improve 17.49%)