Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Closed
-
3.3.2
-
None
Description
For some reason, we are seeing this query constantly show up in slow query logs and taking up 20-30 seconds to evaluate fully, and I somehow traced it to Smartfeed extension.
SELECT f.*, t.*, p.*, u.*,
|
tt.mark_time AS topic_mark_time,
|
ft.mark_time AS forum_mark_time
|
FROM (phpbb_forums f CROSS JOIN phpbb_topics t CROSS JOIN phpbb_posts p CROSS JOIN phpbb_users u)
|
LEFT JOIN phpbb_topics_track tt ON (t.topic_id = tt.topic_id AND tt.user_id = u.user_id)
|
LEFT JOIN phpbb_forums_track ft ON (f.forum_id = ft.forum_id AND ft.user_id = u.user_id)
|
WHERE f.forum_id = t.forum_id
|
AND t.topic_id = p.topic_id
|
AND p.poster_id = u.user_id
|
AND p.post_time > 1639128751 |
AND p.forum_id IN (1, 9, 8, 6, 4, 14, 15, 18, 22, 24, 25, 23, 21, 28, 31, 32, 33, 34, 38, 39, 35, 40, 41, 44, 45, 49, 50, 7, 54, 56, 59, 61, 60, 65, 66, 67, 71, 69, 73, 78, 75, 79, 82, 81, 85, 87, 90, 94, 96) |
AND p.post_visibility = 1 |
AND forum_password = '' |
AND topic_status <> 2 |
ORDER BY f.left_id, f.right_id, t.topic_last_post_time, p.post_time
|
LIMIT 18446744073709551615 |
If I run it manually in phpMyAdmin, it literally locks it up and I have to manually kill that process as it never finished evaluating in there.
Is there any way this query can be optimized somehow?