SELECT action_type, COUNT(*) count
FROM user_actions a
JOIN topics t ON t.id = a.target_topic_id
LEFT JOIN posts p on p.id = a.target_post_id
JOIN posts p2 on p2.topic_id = a.target_topic_id and p2.post_number = 1
LEFT JOIN categories c ON c.id = t.category_id
WHERE (a.user_id = 12) AND (t.deleted_at is null)
GROUP BY action_type
##Query 2
Executing action: show
T+1481.2 ms
Reader 48.8 ms
SELECT COUNT(*) FROM "topics" LEFT OUTER JOIN topic_users AS tu ON (topics.id = tu.topic_id AND tu.user_id = 12) WHERE ("topics"."deleted_at" IS NULL) AND (topics.id IN (SELECT topic_id FROM topic_allowed_users WHERE user_id = 12)) AND "topics"."archetype" = 'private_message' AND (tu.last_read_post_number IS NULL OR tu.last_read_post_number < topics.highest_post_number)
##Query 3
Although this one is not that big - it’s very simular to the one above and runs just before it
SELECT COUNT(*) FROM "topics" LEFT OUTER JOIN topic_users AS tu ON (topics.id = tu.topic_id AND tu.user_id = 12) WHERE ("topics"."deleted_at" IS NULL) AND (topics.id IN (SELECT topic_id FROM topic_allowed_users WHERE user_id = 12)) AND "topics"."archetype" = 'private_message' AND "topics"."user_id" = 12
I can now report that the user card loading times… Discourse 1.2.0.beta5 - https://github.com/discourse/discourse version 80d8b133d948c742275052123cad6ba753937ae0
The loading time is clearly linked to the number of topics / posts a user has.
Numbers from profiler.
When selecting an older user with 5540 posts the time is ~2045ms (feels like 2.8 seconds to display), with the slowest query:
That same user 12 from the previous slow query had ~3803 posts - it appears I have clicked this user too many times and the cached time is ~617ms (feels like ~2 seconds).