# Query lente in Discourse

**URL:** <https://meta.discourse.org/t/slow-queries-in-discourse/152494>\
**Category:** Development\
**Created:** [24 Maggio 2020, 2:47am UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494 "2020-05-24T02:47:11Z")\
**Posts on this page:** 1\
**Showing post:** 9

<div class="post-metadata">

**Author:** ![wtune](https://avatars.discourse-cdn.com/v4/letter/w/8dc957/32.png) [@wtune](https://meta.discourse.org/u/wtune)\
**Post date:** [24 Maggio 2020, 4:10am UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/9 "2020-05-24T04:10:09Z")

</div>

Dal database rileviamo una query lenta (1141257 nanosecondi) con JOIN e DISTINCT non necessari, generata da [directory\_items\_controller.rb](https://github.com/discourse/discourse/blob/61d8955fcb165d2a55760279928306b47fb98898/app/controllers/directory_items_controller.rb#L7-L82), come segue:

```plaintext
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

```

Non ha recuperato dati da user\_stats e groups.id = 2898 può essere sostituito da group\_users.group\_id = 2898, il che significa che possiamo rimuovere le tabelle groups e user\_stats dalla query. Allo stesso tempo, grazie all’indice UNIQUE (user\_id, group\_id) in group\_users e alla PRIMARY KEY (id) in users, il JOIN non crea record duplicati, quindi possiamo anche rimuovere DISTINCT dalla query, come mostrato di seguito:

```plaintext
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

```

Questa query ottimizzata impiega 941700 nanosecondi (miglioramento del 17,49%).

---

_[View the full topic](https://meta.discourse.org/t/slow-queries-in-discourse/152494)._