GigaPeon fucked around with this message at 02:28 on Nov 15, 2023

GigaPeon fucked around with this message at 05:02 on Nov 17, 2023

GigaPeon fucked around with this message at 07:04 on Nov 17, 2023

GigaPeon fucked around with this message at 00:32 on Nov 22, 2023

Array<{
  "id": 123,
  "body": "hello world",
  "author_username": "teen phone cutie",
  "author_id": 1,
  "thread_id": 123,
  ...more_post_and_thread_info
  "active_offense": {
    "is_ban": true,
    "id": 123,
    ...more_info_about_offense
  },
  "author_total_posts": 400 // this is the data that I need that's causing the slowdown
}>

SELECT   posts_1.id,
                posts_1.body,
                posts_1.thread_id,
                posts_1.author_id,
                threads.title,
                users.username,
                users.byline,
                forums.id,
                forums.title,
                Group_concat(offenses_1.id),
                Group_concat(offenses_1.is_ban),
                Group_concat(COALESCE(offenses_1.end_time, "2024-01-14T13:01:52.352914Z")),
                Group_concat(offenses_1.post_id),
                offense_lat.id,
                offense_lat.is_ban,
                offense_lat.end_time,
                post_count_lat.count_1
FROM            posts AS posts_1
INNER JOIN      threads
ON              posts_1.thread_id = threads.id
AND             threads.is_deleted = false
AND             posts_1.thread_id = 11
INNER JOIN      forums
ON              forums.id = threads.forum_id
INNER JOIN      users
ON              users.id = posts_1.author_id
LEFT OUTER JOIN offenses AS offenses_1
ON              offenses_1.post_id = posts_1.id
AND             offenses_1.approved_user_id > 0
LEFT OUTER JOIN lateral
                (
                         select   offenses_2.id                      AS id,
                                  offenses_2.end_time                AS end_time,
                                  offenses_2.is_ban                  AS is_ban,
                                  offenses_2.post_id                 AS post_id,
                                  offenses_2.private_message_post_id AS private_message_post_id
                         FROM     offenses                           AS offenses_2
                         WHERE    (
                                           offenses_2.is_ban = true
                                  OR       offenses_2.end_time > "2024-01-14T13:01:52.352914Z")
                         AND      offenses_2.approved_user_id > 0
                         AND      offenses_2.offender_id = posts_1.author_id
                         ORDER BY offenses_2.is_ban DESC,
                                  offenses_2.end_time DESC limit 1) AS offense_lat
ON              true = 1
LEFT OUTER JOIN lateral
                (
                           SELECT     count(posts_2.thread_id) AS count_1
                           FROM       posts                    AS posts_2
                           INNER JOIN threads
                           ON         threads.id = posts_2.thread_id
                           WHERE      posts_1.author_id = posts_2.author_id
                           AND        threads.is_deleted = false) AS post_count_lat
ON              true = 1
WHERE           threads.id = 11
GROUP BY        posts_1.id,
                offense_lat.id,
                post_count_lat.count_1
ORDER BY        posts_1.posted_at ASC
LIMIT 25
OFFSET 475

teen phone cutie fucked around with this message at 02:03 on Jan 15, 2024

teen phone cutie fucked around with this message at 02:22 on Jan 15, 2024

RandomBlue fucked around with this message at 02:45 on Jan 15, 2024

WHERE (
	(
		NULL IS NULL AND v.contract_nbr = 0
	) 
	OR
	(
		v.contract_nbr > 0 AND (
			(
				NULL IS NULL AND 'Y' = 'Y'
			) OR (
				NULL IS NOT NULL AND offsite_storage_id IS NULL
			)
		)
	)
)

kiwid fucked around with this message at 20:02 on Jan 31, 2024