À partir du journal de la base de données, nous constatons que certaines requêtes contiennent un ORDER BY inutile dans une sous-requête générée par user.rb#L379. L’une d’elles est la suivante :
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
L’utilisation de ORDER BY dans une sous-requête n’a aucun sens et peut être supprimée pour accélérer la requête. Selon nos tests, cela peut améliorer les performances de la requête de 18 % à 90 % (selon le temps perdu dans le tri de la sous-requête).
Nous exécutons des spécifications dans Discourse et, à partir de la base de données, nous avons détecté une requête lente (64 802 590 nanosecondes, comparée à une requête similaire) générée par un DISTINCT inutile dans site_settings_controller.rb#L141, comme suit :
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
)
Lorsque les valeurs de tags.id et notification_level sont spécifiées, en raison de l’unicité (tag_id, user_id, notification_level) dans tag_users et de la clé primaire (id) dans tags, ni le CROSS JOIN ni le LEFT JOIN ne créent de doublons. Cela signifie que nous pouvons supprimer DISTINCT pour accélérer la requête.
Cette requête optimisée s’exécute en 45 388 910 nanosecondes (amélioration de 30 %).
À partir du journal de la base de données, nous détectons une requête lente (60 643 799 nanosecondes) comportant de nombreuses sous-requêtes et des unions, générée par group.rb#L112, comme suit :
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
Il est évident que cela équivaut à la requête suivante :
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
Cette requête optimisée s’exécute en 37 806 200 nanosecondes (amélioration de 93 %).
Je suis désolé, la requête réécrite correcte devrait être :
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
La sous-requête avec le prédicat visibility_level = 0,1,2 peut être remplacée par groups.visibility_level IN (0, 1, 2), et la sous-requête avec le prédicat visibility_level = 3,4 peut être remplacée par OR (groups.visibility_level IN (3,4) AND gu.owner).
Nous avons détecté dans la base de données une requête lente (11 412 570 nanosecondes) avec des JOIN et un DISTINCT inutiles, générée par directory_items_controller.rb, comme suit :
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
Cette requête ne récupère aucune donnée dans user_stats, et la condition groups.id = 2898 peut être remplacée par group_users.group_id = 2898. Cela signifie que nous pouvons supprimer les tables groups et user_stats de la requête. Par ailleurs, grâce à l’index UNIQUE (user_id, group_id) sur group_users et la clé primaire PRIMARY KEY (id) sur users, les JOIN ne créent pas de doublons ; nous pouvons donc également supprimer le DISTINCT, comme illustré ci-dessous :
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
Cette requête optimisée s’exécute en 9 417 000 nanosecondes (amélioration de 17,49 %).
Nous exécutons les tests dans Discourse et, depuis la base de données, nous identifions une requête lente (331729 nanosecondes, comparée à une requête similaire) générée par un JOIN inutile provenant de ser_badge.rb#L18, comme suit :
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
Cette sous-requête peut être remplacée par un simple prédicat badges.enabled, comme illustré ci-dessous :
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
Cette requête optimisée s’exécute en 267383 nanosecondes (amélioration de 19 %).