# Slow queries in Discourse
**URL:** https://meta.discourse.org/t/slow-queries-in-discourse/152494
**Category:** Development
**Created:** [24 mei 2020 om 02:47 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494 "2020-05-24T02:47:11Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@wtune](https://meta.discourse.org/u/wtune)
#### Post date: [24 mei 2020 om 02:47 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/1 "2020-05-24T02:47:11Z")
From Database Log we find some queries with unnecessary ORDER BY in subquery produced by [user.rb#L379](https://github.com/discourse/discourse/blob/61d8955fcb165d2a55760279928306b47fb98898/app/models/user.rb#L379).One of them is as follows:
```plaintext
SELECT
"group_users"."group_id"
FROM
"group_users"
WHERE
"group_users"."group_id" IN (
SELECT
"groups"."id"
FROM
"groups"
WHERE
(groups.id > 0)
ORDER BY
name ASC
)
AND "group_users"."user_id" = 762
```
ORDER BY in subquery is meaningless and we can remove it to accelerate query. According to our test it can improve performance of query by 18%-90%(depend on the time wateing on sort in subquery)
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [24 mei 2020 om 03:02 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/2 "2020-05-24T03:02:50Z")
What are the actual benchmarks, in milliseconds?
---
### Author:  [@wtune](https://meta.discourse.org/u/wtune)
#### Post date: [24 mei 2020 om 03:19 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/3 "2020-05-24T03:19:59Z")
For this example, remove ORDER BY can decrease query time from 4711878 Nanosecond to 585849 Nanosecond.
---
### Author:  [@wtune](https://meta.discourse.org/u/wtune)
#### Post date: [25 mei 2020 om 07:29 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/4 "2020-05-25T07:29:10Z")
We run specs in discourse, and from Database we find a slow query((6480259 nanosecond) compared with similar query) with unnecessary DISTINCT produced by
[site\_settings\_controller.rb#L141](https://github.com/discourse/discourse/blob/61d8955fcb165d2a55760279928306b47fb98898/app/controllers/admin/site_settings_controller.rb#L141)as follows:
```plaintext
SELECT
DISTINCT users.id
FROM
"users" CROSS
JOIN tags t
LEFT JOIN tag_users tu ON users.id = tu.user_id
AND t.id = tu.tag_id
WHERE
(
t.id IN (1825)
AND tu.notification_level IS NULL
)
```
When tags.id and notification\_level are specify value, because of UNIQUE (tag\_id, user\_id, notification\_level) in tag\_users and PRIMARY KEY(id) in tags, both cross join and left join will not create duplicate
records which means that we can remove DISTINCT to accelerate query.
This opt query take 4538891 nanosecond(improve 30%)
---
### Author:  [@wtune](https://meta.discourse.org/u/wtune)
#### Post date: [24 mei 2020 om 04:28 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/6 "2020-05-24T04:28:07Z")
From Database Log we find a slow query(6064379 NanoSecond) with many subquery and union produced by [group.rb#L112](https://github.com/discourse/discourse/blob/61d8955fcb165d2a55760279928306b47fb98898/app/models/group.rb#L112) as follows:
```plaintext
SELECT
"groups"."id",
"groups"."name"
FROM
"groups"
INNER JOIN "group_users" ON "groups"."id" = "group_users"."group_id"
WHERE
"group_users"."user_id" = 296
AND (groups.id > 0)
AND (
groups.id IN (
SELECT
id
FROM
groups
WHERE
visibility_level = 0
UNION ALL
SELECT
id
FROM
groups
WHERE
visibility_level = 1
AND 296 IS NOT NULL
UNION ALL
SELECT
g.id
FROM
groups g
JOIN group_users gu ON gu.group_id = g.id
AND gu.user_id = 296
WHERE
g.visibility_level = 2
UNION ALL
SELECT
g.id
FROM
groups g
LEFT JOIN group_users gu ON gu.group_id = g.id
AND gu.user_id = 296
AND gu.owner
WHERE
g.visibility_level = 3
AND (
gu.id IS NOT NULL
OR FALSE
)
UNION ALL
SELECT
g.id
FROM
groups g
JOIN group_users gu ON gu.group_id = g.id
AND gu.user_id = 296
AND gu.owner
WHERE
g.visibility_level = 4
)
)
ORDER BY
name ASC
```
Obviously it is equal to query as below:
```plaintext
SELECT
"groups"."id",
"groups"."name"
FROM
"groups"
INNER JOIN "group_users" ON "groups"."id" = "group_users"."group_id"
WHERE
"group_users"."user_id" = 296 AND
groups.visibility_level IN (0, 1, 2, 3, 4)
ORDER BY
name ASC
```
This opt query takes 378062 Nanosecond(improve 93%).
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [24 mei 2020 om 05:25 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/7 "2020-05-24T05:25:16Z")
> [@wtune](#):
>
> Obviously it is equal to query as below:
How is it equal? you are looking for groups with no visibility level checks.
---
### Author:  [@wtune](https://meta.discourse.org/u/wtune)
#### Post date: [25 mei 2020 om 05:56 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/8 "2020-05-25T05:56:55Z")
I‘m sorry , the correct rewritten query should be
```plaintext
SELECT
"groups"."id",
"groups"."name"
FROM
"groups"
INNER JOIN "group_users" gu ON "groups"."id" = gu."group_id"
WHERE
"group_users"."user_id" = 296
AND (groups.id > 0)
AND (
groups.visibility_level IN (0, 1, 2)
OR (groups.visibility_level IN (3,4) and gu.owner)
)
ORDER BY
name ASC
```
subquery with predicate visibility\_level = 0,1,2 can be replaced by groups.visibility\_level IN (0, 1, 2) , and subquery with predicate visibility\_level = 3,4 can be replaced by or (groups.visibility\_level IN (3,4) and gu.owner)
---
### Author:  [@wtune](https://meta.discourse.org/u/wtune)
#### Post date: [24 mei 2020 om 04:10 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/9 "2020-05-24T04:10:09Z")
From Database we find a slow query(1141257 Nanosecond) with unnecessary JOIN and DISTINCT produced by [directory\_items\_controller.rb](https://github.com/discourse/discourse/blob/61d8955fcb165d2a55760279928306b47fb98898/app/controllers/directory_items_controller.rb#L7-L82) as follows
```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
```
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:
```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
```
This opt query take 941700 Nanosecond(improve 17.49%)
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [24 mei 2020 om 06:19 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/10 "2020-05-24T06:19:08Z")
Are you running some kind of database scanner on Discourse?
---
### Author:  [@wtune](https://meta.discourse.org/u/wtune)
#### Post date: [25 mei 2020 om 07:13 UTC](https://meta.discourse.org/t/slow-queries-in-discourse/152494/11 "2020-05-25T07:13:16Z")
We run the specs in discourse, and from Database we find a slow query((331729 nanosecond)compared with similar query) with unnecessary JOIN produced by
[ser\_badge.rb#L18](https://github.com/discourse/discourse/blob/61d8955fcb165d2a55760279928306b47fb98898/app/models/user_badge.rb#L18)as follows:
```plaintext
SELECT
COUNT(*)
FROM
"badges"
INNER JOIN "user_badges" ON "badges"."id" = "user_badges"."badge_id"
WHERE
"user_badges"."user_id" = 2112
AND (
user_badges.badge_id IN (
SELECT
id
FROM
badges
WHERE
enabled
)
)
AND "badges"."id" = 1
```
This subquery can be replaced by a simple prediacte badges.enabled as shown below:
```plaintext
SELECT
COUNT(*)
FROM
"badges"
INNER JOIN "user_badges" ON "badges"."id" = "user_badges"."badge_id"
WHERE
"user_badges"."user_id" = 2112
AND badges.enabled
AND "badges"."id" = 1
```
This opt query take 267383 nanosecond(improve 19%)