Possible database index to improve looking for new messages

[ChristophWurst]

Is your feature request related to a problem? Please describe.

There are three common queries run by this app:

Looking for new messages:

SELECT `m`.`id`,
            `m`.`sent_at`
        FROM
            `oc_mail_messages` `m`
                LEFT JOIN `oc_mail_messages` `m2` ON (`m`.`mailbox_id` = `m2`.`mailbox_id`)
                AND (
                                                         `m`.`thread_root_id` = `m2`.`thread_root_id`
                                                         )
                AND (
                                                         (`m`.`sent_at` < `m2`.`sent_at`)
                                                             OR (
                                                             (`m`.`sent_at` = `m2`.`sent_at`)
                                                                 AND (`m`.`message_id` < `m2`.`message_id`)
                                                             )
                                                         )
        WHERE
            (`m`.`mailbox_id` = 26)
          AND (`m`.`id` NOT IN (20))
          AND (`m2`.`id` IS NULL)
          AND (
            `m`.`sent_at` > (
                SELECT
                    MIN(`sent_at`)
                FROM
                    `oc_mail_messages`
                WHERE
                    (`mailbox_id` = 26)
                  AND (`id` IN (70733))
            )
            )
        ORDER BY
            `m`.`sent_at` DESC

Important messages dashboard widget:

SELECT
  `m`.`id`,
  `m`.`sent_at`
FROM
  `oc_mail_messages` `m`
  LEFT JOIN `oc_mail_messages` `m2` ON (`m`.`mailbox_id` = `m2`.`mailbox_id`)
  AND (
    `m`.`thread_root_id` = `m2`.`thread_root_id`
  )
  AND (
    (`m`.`sent_at` < `m2`.`sent_at`)
    OR (
      (`m`.`sent_at` = `m2`.`sent_at`)
      AND (`m`.`message_id` < `m2`.`message_id`)
    )
  )
WHERE
  (
    `m`.`mailbox_id` IN (
      SELECT
        `mb`.`id`
      FROM
        `oc_mail_mailboxes` `mb`
        INNER JOIN `oc_mail_accounts` `a` ON `a`.`id` = `mb`.`account_id`
      WHERE
        `a`.`user_id` = ?
    )
  )
  AND (`m`.`flag_important` = 1)
  AND (`m2`.`id` IS NULL)
ORDER BY
  `m`.`sent_at` DESC
LIMIT
  10

Unread messages dashboard widget:

SELECT
  `m`.`id`,
  `m`.`sent_at`
FROM
  `oc_mail_messages` `m`
  LEFT JOIN `oc_mail_messages` `m2` ON (`m`.`mailbox_id` = `m2`.`mailbox_id`)
  AND (
    `m`.`thread_root_id` = `m2`.`thread_root_id`
  )
  AND (
    (`m`.`sent_at` < `m2`.`sent_at`)
    OR (
      (`m`.`sent_at` = `m2`.`sent_at`)
      AND (`m`.`message_id` < `m2`.`message_id`)
    )
  )
WHERE
  (
    `m`.`mailbox_id` IN (
      SELECT
        `mb`.`id`
      FROM
        `oc_mail_mailboxes` `mb`
        INNER JOIN `oc_mail_accounts` `a` ON `a`.`id` = `mb`.`account_id`
      WHERE
        `a`.`user_id` = ?
    )
  )
  AND (`m`.`flag_seen` = ?)
  AND (`m2`.`id` IS NULL)
ORDER BY
  `m`.`sent_at` DESC
LIMIT
  ?

Describe the solution you'd like

 create index mail_msg_test_20241216_new_messages_join
    on oc_mail_messages (mailbox_id, sent_at, flag_important, id);

Describe alternatives you've considered

No response

Additional context

No response