Possible database index to improve looking for new messages

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

The issue names the oc_mail_messages table and three SQL queries, but no source file or test. Start by locating the schema or migration entry point for that table, then reproduce or measure the listed queries and verify that the proposed index improves the affected lookups without regressions.