High CPU usage on overboards's catalog #133

New issue

Open

opened 2025-07-06 15:33:16 -05:00 by Zankaria · 1 comment

Zankaria commented

2025-07-06 15:33:16 -05:00

Owner

private function buildThreadsQuery($board) {
    $sql  = "SELECT *, `id` AS `thread_id`, " .
        "(SELECT COUNT(`id`) FROM ``posts_$board`` WHERE `thread` = `thread_id`) AS `reply_count`, " .
        "(SELECT SUM(`num_files`) FROM ``posts_$board`` WHERE `thread` = `thread_id` AND `num_files` IS NOT NULL) AS `image_count`, " .
        "'$board' AS `board` FROM ``posts_$board`` WHERE `thread` IS NULL";

    return $sql;
}

Over

private function buildThreadsQuery($board) {

gets called by

public function buildOverboard($board_name, $settings, $boards, $mod = false) {

, which results in a monstrous query that sucks up all CPU resources

``` private function buildThreadsQuery($board) { $sql = "SELECT *, `id` AS `thread_id`, " . "(SELECT COUNT(`id`) FROM ``posts_$board`` WHERE `thread` = `thread_id`) AS `reply_count`, " . "(SELECT SUM(`num_files`) FROM ``posts_$board`` WHERE `thread` = `thread_id` AND `num_files` IS NOT NULL) AS `image_count`, " . "'$board' AS `board` FROM ``posts_$board`` WHERE `thread` IS NULL"; return $sql; } ``` Over https://forgejo.leftypol.org/leftypol/leftypol/src/commit/9878b0c4d2e05d349a2cfe4c7208259fbaf18a4e/templates/themes/catalog/theme.php#L279 gets called by https://forgejo.leftypol.org/leftypol/leftypol/src/commit/9878b0c4d2e05d349a2cfe4c7208259fbaf18a4e/templates/themes/catalog/theme.php#L200, which results in a monstrous query that sucks up all CPU resources

Zankaria added the

Bug

Proposal

labels 2025-07-06 15:33:16 -05:00

Zankaria commented

2025-07-06 15:36:50 -05:00

Author

Owner

Proposal by @perdedora (reformatted)

WITH thread_data AS (
	SELECT id AS thread_id,	COUNT(id) AS reply_count, SUM(num_files) AS image_count, 'b' AS board
	FROM posts_b
	WHERE thread IS null
	GROUP BY id
)
SELECT td.*, p.*
FROM posts_b p
INNER JOIN thread_data td ON p.id = td.thread_id
WHERE p.thread IS null

Proposal by @perdedora (reformatted) ``` WITH thread_data AS ( SELECT id AS thread_id, COUNT(id) AS reply_count, SUM(num_files) AS image_count, 'b' AS board FROM posts_b WHERE thread IS null GROUP BY id ) SELECT td.*, p.* FROM posts_b p INNER JOIN thread_data td ON p.id = td.thread_id WHERE p.thread IS null ```

Zankaria added reference ukko-catalog-sql-perf

2025-07-06 15:57:25 -05:00

No labels

Bug

Proposal

Request

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference: leftypol/leftypol#133

No description provided.

Rows
Columns