Possible indexes

[rullzer]

We noticed some slow queries (10+ seconds). After these indexes the slow query log stays very empty:

CREATE INDEX mail_messages_id_flags ON oc_mail_messages (mailbox_id, flag_important, flag_deleted, flag_seen);

CREATE INDEX mail_messages_id_flags2 ON oc_mail_messages (mailbox_id, flag_deleted, flag_flagged);

The names are not ideal and the queries might also not be. But it is a start to continue on.

[ChristophWurst]

[rullzer]

[rullzer]

Also foudna nother query that is not using proper indexes:

SELECT * FROM `oc_mail_messages`
WHERE (`mailbox_id` IN (
  SELECT `id` 
  FROM `oc_mail_mailboxes` 
  WHERE `account_id` = 33)
) AND (
  (`id` = 1730972) 
  OR (
    (`thread_root_id` IS NOT NULL) 
    AND (`thread_root_id` IN (
      SELECT `thread_root_id` 
      FROM `oc_mail_messages` 
      WHERE `id` = 1730972
      )
    )
  )
) ORDER BY `sent_at` desc;

[ChristophWurst]

Using a join instead of subquery 🚀

SELECT m2.* FROM oc_mail_messages m1 LEFT JOIN oc_mail_messages m2 ON m1.thread_root_id = m2.thread_root_id WHERE m1.id = 229382 AND m1.mailbox_id IN (SELECT `id`    FROM `oc_mail_mailboxes`    WHERE `account_id` = 1248);

[miaulalala]

Plus it needs an index on thread_root_id

[ChristophWurst]

Little big detail 😉

[rullzer]

Plus it needs an index on thread_root_id

Probably makes sense anyway

[ChristophWurst]

Nope, without restructuring at least Mysql won't pick up the index

[miaulalala]

Still needs the migration for the thread_root_id index, but this needs to wait until there's a solve for an issue related to #25 , i.e. how to store flags for users that aren't able to use any permflag capabilites because their IMAP doesn't support them. Still need to figure out how to save "important" flags for those users when resetting the cache.