Redmine

Time entry details fetch query load issue.

when, i remove where clause query gets executed quickly.(I know the where clause is important to validate the module is enabled for the project).
In JOIN clauses, The query validates issue_tracking module is enabled or not.
again in WHERE Clause, The validation happens for time_tracking module.
In this case, when join exec happens 4496 columns filtered
when exec move to LEFT OUTER JOIN, issue_tracking module validation. 1181 rows get filtered.
Now in where clause another 1181 again.
(4496 * 1181 * 1181) now of rows examined in query is inceased.

SELECT
    `time_entries`.`id` AS t0_r0,
    `time_entries`.`project_id` AS t0_r1,
    `time_entries`.`author_id` AS t0_r2,
    `time_entries`.`user_id` AS t0_r3,
    `time_entries`.`issue_id` AS t0_r4,
    `time_entries`.`hours` AS t0_r5,
    `time_entries`.`comments` AS t0_r6,
    `time_entries`.`activity_id` AS t0_r7,
    `time_entries`.`spent_on` AS t0_r8,
    `time_entries`.`tyear` AS t0_r9,
    `time_entries`.`tmonth` AS t0_r10,
    `time_entries`.`tweek` AS t0_r11,
    `time_entries`.`created_on` AS t0_r12,
    `time_entries`.`updated_on` AS t0_r13,
    `enumerations`.`id` AS t1_r0,
    `enumerations`.`name` AS t1_r1,
    `enumerations`.`position` AS t1_r2,
    `enumerations`.`is_default` AS t1_r3,
    `enumerations`.`type` AS t1_r4,
    `enumerations`.`active` AS t1_r5,
    `enumerations`.`project_id` AS t1_r6,
    `enumerations`.`parent_id` AS t1_r7,
    `enumerations`.`position_name` AS t1_r8
FROM
    `time_entries`
    INNER JOIN `projects` ON `projects`.`id` = `time_entries`.`project_id`
    INNER JOIN `enumerations` ON `enumerations`.`id` = `time_entries`.`activity_id`
    AND `enumerations`.`type` = 'TimeEntryActivity'
    INNER JOIN `users` ON `users`.`id` = `time_entries`.`user_id`
    AND `users`.`type` IN ('User', 'AnonymousUser')
    LEFT OUTER JOIN issues ON issues.id = time_entries.issue_id
    AND (
        projects.status IN (1, 5)
        AND EXISTS (
            SELECT
                1 AS one
            FROM
                enabled_modules em
            WHERE
                em.project_id = projects.id
                AND em.name = 'issue_tracking'
        )
    )
WHERE
    (
        projects.status IN (1, 5)
        AND EXISTS (
            SELECT
                1 AS one
            FROM
                enabled_modules em
            WHERE
                em.project_id = projects.id
                AND em.name = 'time_tracking'
        )
    )
ORDER BY
    time_entries.spent_on DESC,
    time_entries.created_on DESC,
    time_entries.id ASC
LIMIT
    25 OFFSET 0

Mysql : 8.0.37
Ruby : 3.2.4
Raile: 7.2.2