mail icon indicating copy to clipboard operation
mail copied to clipboard
verified publisher icon nextcloud

v3.5+ DB performance analysis

Open ChristophWurst opened this issue 2 years ago • 7 comments

Steps to reproduce

  1. Use the app

Expected behavior

Fast DB operations

Actual behavior

?

Mail app version

v3.5.0 RC3 / RC4

Mailserver or service

No response

Operating system

No response

PHP engine version

None

Web server

None

Database

None

Additional info

Queries of my personal instance for roughly a day of use with two mail accounts:

 133752 UPDATE `oc_mail_messages` SET `flag_answered` = :flag_answered, `flag_deleted` = :flag_deleted, `flag_draft` = :flag_draft, `flag_flagged` = :flag_flagged, `flag_seen` = :flag_seen, `flag_forwarded` = :flag_forwarded, `flag_junk` = :flag_junk, `flag_notjunk` = :flag_notjunk, `flag_mdnsent` = :flag_mdnsent, `flag_important` = :flag_important, `updated_at` = :dcValue1 WHERE (`uid` = :uid) AND (`mailbox_id` = :mailbox_id)
   3774 UPDATE `oc_mail_mailboxes` SET `sync_new_lock` = :dcValue1, `sync_changed_lock` = :dcValue2, `sync_vanished_lock` = :dcValue3 WHERE `id` = :dcValue4
   1811 SELECT DISTINCT `t`.*, `mt`.`imap_message_id` FROM `oc_mail_tags` `t` INNER JOIN `oc_mail_message_tags` `mt` ON `t`.`id` = `mt`.`tag_id` WHERE (`mt`.`imap_message_id` IN (:ids)) AND (`t`.`user_id` = :dcValue1)
   1126 SELECT `uid` FROM `oc_mail_messages` WHERE (`mailbox_id` = :dcValue1) AND (`id` IN (:ids))
   1018 SELECT `label`, `email`, `type`, `message_id` FROM `oc_mail_recipients` WHERE `message_id` IN (:ids)
    909 SELECT * FROM `oc_mail_accounts` WHERE (`user_id` = :dcValue1) AND (`id` = :dcValue2)
    883 SELECT `mb`.* FROM `oc_mail_mailboxes` `mb` INNER JOIN `oc_mail_accounts` `a` ON `mb`.`account_id` = `a`.`id` WHERE (`a`.`user_id` = :dcValue1) AND (`mb`.`id` = :dcValue2)
    852 SELECT * FROM `oc_mail_messages` WHERE (`mailbox_id` = :dcValue1) AND (`id` IN (:ids)) ORDER BY `sent_at` desc
    811 UPDATE `oc_mail_mailboxes` SET `sync_new_lock` = :dcValue1 WHERE (`id` = :dcValue2) AND (`sync_new_lock` IS NULL)
    797 UPDATE `oc_mail_mailboxes` SET `sync_vanished_lock` = :dcValue1 WHERE (`id` = :dcValue2) AND (`sync_vanished_lock` IS NULL)
    797 UPDATE `oc_mail_mailboxes` SET `sync_changed_lock` = :dcValue1 WHERE (`id` = :dcValue2) AND (`sync_changed_lock` IS NULL)
    797 SELECT MAX(`uid`) FROM `oc_mail_messages` WHERE `mailbox_id` = :dcValue1
    709 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`) WHERE (`m`.`mailbox_id` = :dcValue2) AND (`m`.`id` NOT IN (:ids)) AND (`m2`.`id` IS NULL) AND (`m`.`sent_at` > (SELECT MIN(`sent_at`) FROM `oc_mail_messages` WHERE (`mailbox_id` = :dcValue1) AND (`id` IN (:ids)))) ORDER BY `m`.`sent_at` desc
    398 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`) WHERE (`m`.`mailbox_id` = :dcValue1) AND (`m`.`uid` IN (:uids)) AND (`m`.`flag_important` = :dcValue2) AND (`m2`.`id` IS NULL) ORDER BY `sent_at` DESC
    348 SELECT * FROM `oc_mail_tags` WHERE (`imap_label` = :dcValue1) AND (`user_id` = :dcValue2)
    334 INSERT INTO `oc_mail_message_tags` (`imap_message_id`, `tag_id`) VALUES(:dcValue1, :dcValue2)
    303 DELETE FROM `oc_mail_message_tags` WHERE (`imap_message_id` = :dcValue1) AND (`tag_id` = :dcValue2)
    287 SELECT * FROM `oc_mail_accounts`
    218 SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = :dcValue1
    155 SELECT * FROM `oc_mail_local_messages` WHERE (`send_at` IS NOT NULL) AND (`type` = :dcValue1) AND (`send_at` <= :dcValue2) AND ((`failed` IS NULL) OR (`failed` = :dcValue3)) ORDER BY `send_at` asc
    154 SELECT * FROM `oc_mail_local_messages` WHERE (`send_at` IS NULL) AND (`type` = :dcValue1) AND (`updated_at` <= :dcValue2) AND ((`failed` IS NULL) OR (`failed` = :dcValue3)) ORDER BY `account_id` asc
    100 DELETE FROM `oc_mail_classifiers` WHERE `id` = :dcValue1
     97 SELECT `m`.* FROM `oc_mail_messages` `m` INNER JOIN `oc_mail_mailboxes` `mb` ON `m`.`mailbox_id` = `mb`.`id` INNER JOIN `oc_mail_accounts` `a` ON `mb`.`account_id` = `a`.`id` WHERE (`a`.`user_id` = :dcValue1) AND (`m`.`id` = :dcValue2)
     90 SELECT * FROM `oc_mail_accounts` WHERE `id` = :dcValue1
     88 UPDATE `oc_mail_accounts` SET `last_mailbox_sync` = :dcValue1 WHERE `id` = :dcValue2

it's the number of executions followed by the query

Work packages

  • https://github.com/nextcloud/mail/pull/9206
  • https://github.com/nextcloud/mail/pull/9235
  • https://github.com/nextcloud/mail/issues/9245
  • https://github.com/nextcloud/mail/pull/9267
  • https://github.com/nextcloud/server/pull/43209
  • https://github.com/nextcloud/mail/pull/9295

ChristophWurst avatar Dec 12 '23 09:12 ChristophWurst

An idea for avoiding the self-join of the messages table for mailbox listing:

EXPLAIN SELECT `m`.`id`, `m`.`sent_at`, m.subject 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`) WHERE (`m`.`mailbox_id` = 7876) AND (`m`.`flag_deleted` = 0) AND (`m2`.`id` IS NULL) ORDER BY `sent_at` DESC LIMIT 20


EXPLAIN SELECT id, min(sent_at), thread_root_id, subject FROM oc_mail_messages where mailbox_id = 7876 group by thread_root_id order by max(sent_at) desc limit 20;
or
EXPLAIN SELECT id, subject FROM oc_mail_messages WHERE mailbox_id = 7876 and thread_root_id IN (SELECT thread_root_id FROM oc_mail_messages where mailbox_id = 7876 group by thread_root_id, thread_root_id order by max(sent_at) desc) limit 20;

before/after

ChristophWurst avatar Jan 04 '24 17:01 ChristophWurst

We capture slow queries for CI runs with MySQL. These can be used as indication for queries to optimize, too:

2024-01-10T18:32:27.9489524Z 2024-01-10 18:30:04.533372	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000220	00:00:00.000178	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_coll_addresses`	32	0
2024-01-10T18:32:27.9491492Z 2024-01-10 18:30:04.539520	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000201	00:00:00.000046	1	3	nextcloud	0	0	1	SELECT * FROM `oc_mail_coll_addresses` WHERE (`user_id` = 'testuser') AND ((`email`  COLLATE utf8mb4_general_ci LIKE '%[email protected]%') OR (`display_name`  COLLATE utf8mb4_general_ci LIKE '%[email protected]%'))	32	0
2024-01-10T18:32:27.9493347Z 2024-01-10 18:30:04.561673	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000073	00:00:00.000040	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_coll_addresses`	32	0
2024-01-10T18:32:27.9495093Z 2024-01-10 18:30:04.563365	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000153	00:00:00.000034	2	3	nextcloud	0	0	1	SELECT * FROM `oc_mail_coll_addresses` WHERE (`user_id` = 'testuser') AND ((`email`  COLLATE utf8mb4_general_ci LIKE '%examp%') OR (`display_name`  COLLATE utf8mb4_general_ci LIKE '%examp%'))	32	0
2024-01-10T18:32:27.9496719Z 2024-01-10 18:30:04.567111	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000026	00:00:00.000015	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_coll_addresses`	32	0
2024-01-10T18:32:27.9630794Z 2024-01-10 18:30:04.568615	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000131	00:00:00.000026	1	3	nextcloud	0	0	1	SELECT * FROM `oc_mail_coll_addresses` WHERE (`user_id` = 'testuser') AND (`email`  COLLATE utf8mb4_general_ci LIKE '[email protected]')	32	0
2024-01-10T18:32:27.9632598Z 2024-01-10 18:30:04.571584	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000024	00:00:00.000014	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_coll_addresses`	32	0
2024-01-10T18:32:27.9636659Z 2024-01-10 18:30:04.572914	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000078	00:00:00.000024	0	3	nextcloud	0	0	1	SELECT * FROM `oc_mail_coll_addresses` WHERE (`user_id` = 'testuser') AND (`email`  COLLATE utf8mb4_general_ci LIKE '[email protected]')	32	0
2024-01-10T18:32:27.9640870Z 2024-01-10 18:30:04.575352	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000023	00:00:00.000014	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_coll_addresses`	32	0
2024-01-10T18:32:27.9642717Z 2024-01-10 18:30:04.576778	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000054	00:00:00.000020	1	3	nextcloud	0	0	1	SELECT COUNT(*) FROM `oc_mail_coll_addresses`	32	0
2024-01-10T18:32:27.9645201Z 2024-01-10 18:30:04.580451	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000029	00:00:00.000018	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_coll_addresses`	32	0
2024-01-10T18:32:27.9646891Z 2024-01-10 18:30:04.581599	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000224	00:00:00.000018	3	3	nextcloud	0	0	1	SELECT * FROM `oc_mail_coll_addresses` ORDER BY `id` ASC LIMIT 100	32	0
2024-01-10T18:32:27.9648513Z 2024-01-10 18:30:04.584651	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000026	00:00:00.000016	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_coll_addresses`	32	0
2024-01-10T18:32:27.9650519Z 2024-01-10 18:30:04.610896	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.013099	00:00:00.013005	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_attachments`	32	0
2024-01-10T18:32:27.9652211Z 2024-01-10 18:30:04.635166	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000141	00:00:00.000032	0	3	nextcloud	0	0	1	UPDATE `oc_mail_attachments` SET `local_message_id` = 1 WHERE (`user_id` = 'user45678') AND (`id` IN (1, 2, 3))	32	2
2024-01-10T18:32:27.9653605Z 2024-01-10 18:30:04.781322	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000072	00:00:00.000039	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_attachments`	32	0
2024-01-10T18:32:27.9655076Z 2024-01-10 18:30:04.791477	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000124	00:00:00.000032	0	3	nextcloud	0	0	1	UPDATE `oc_mail_attachments` SET `local_message_id` = 3 WHERE (`user_id` = 'user45678') AND (`id` IN (4, 5, 6))	32	2
2024-01-10T18:32:27.9656441Z 2024-01-10 18:30:04.802468	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000036	00:00:00.000021	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_attachments`	32	0
2024-01-10T18:32:27.9657854Z 2024-01-10 18:30:04.808050	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000219	00:00:00.000083	0	3	nextcloud	0	0	1	UPDATE `oc_mail_attachments` SET `local_message_id` = 5 WHERE (`user_id` = 'user45678') AND (`id` IN (7, 8, 9))	32	2
2024-01-10T18:32:27.9659611Z 2024-01-10 18:30:04.813956	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000029	00:00:00.000017	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_attachments`	32	0
2024-01-10T18:32:27.9661353Z 2024-01-10 18:30:04.851073	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.008757	00:00:00.008657	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9662598Z 2024-01-10 18:30:04.996023	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000125	00:00:00.000022	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '2'	32	0
2024-01-10T18:32:27.9664173Z 2024-01-10 18:30:05.003108	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000178	00:00:00.000048	1	1	nextcloud	0	0	1	SELECT `m`.* FROM `oc_mail_accounts` `a` INNER JOIN `oc_mail_local_messages` `m` ON `m`.`account_id` = `a`.`id` WHERE (`a`.`user_id` = 'user12345') AND (`m`.`type` = 0)	32	0
2024-01-10T18:32:27.9667122Z 2024-01-10 18:30:05.434816	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000124	00:00:00.000076	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9668489Z 2024-01-10 18:30:05.488325	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000124	00:00:00.000024	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '3'	32	0
2024-01-10T18:32:27.9669590Z 2024-01-10 18:30:05.525261	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000077	00:00:00.000045	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9670719Z 2024-01-10 18:30:05.587260	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000103	00:00:00.000017	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '4'	32	0
2024-01-10T18:32:27.9671830Z 2024-01-10 18:30:05.601107	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000108	00:00:00.000058	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9679753Z 2024-01-10 18:30:05.671374	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000175	00:00:00.000049	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '5'	32	0
2024-01-10T18:32:27.9682146Z 2024-01-10 18:30:05.681891	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000178	00:00:00.000043	0	1	nextcloud	0	0	1	SELECT `m`.* FROM `oc_mail_accounts` `a` INNER JOIN `oc_mail_local_messages` `m` ON `m`.`account_id` = `a`.`id` WHERE (`a`.`user_id` = 'user12345') AND (`m`.`type` = 0)	32	0
2024-01-10T18:32:27.9683697Z 2024-01-10 18:30:05.690883	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000098	00:00:00.000064	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9684920Z 2024-01-10 18:30:05.761218	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000143	00:00:00.000033	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '6'	32	0
2024-01-10T18:32:27.9686651Z 2024-01-10 18:30:05.777841	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000077	00:00:00.000032	0	1	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	1
2024-01-10T18:32:27.9688235Z 2024-01-10 18:30:05.809386	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000404	00:00:00.000083	1	1	nextcloud	0	0	1	SELECT `m`.* FROM `oc_mail_accounts` `a` INNER JOIN `oc_mail_local_messages` `m` ON `m`.`account_id` = `a`.`id` WHERE (`a`.`user_id` = 'user12345') AND (`m`.`type` = 0)	32	0
2024-01-10T18:32:27.9689747Z 2024-01-10 18:30:05.833152	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000085	00:00:00.000055	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9691078Z 2024-01-10 18:30:05.906735	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000105	00:00:00.000020	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '7'	32	0
2024-01-10T18:32:27.9692600Z 2024-01-10 18:30:05.917704	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000226	00:00:00.000076	1	1	nextcloud	0	0	1	SELECT `m`.* FROM `oc_mail_accounts` `a` INNER JOIN `oc_mail_local_messages` `m` ON `m`.`account_id` = `a`.`id` WHERE (`a`.`user_id` = 'user12345') AND (`m`.`type` = 0)	32	0
2024-01-10T18:32:27.9694321Z 2024-01-10 18:30:05.924184	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000138	00:00:00.000033	1	1	nextcloud	0	0	1	SELECT `m`.* FROM `oc_mail_accounts` `a` INNER JOIN `oc_mail_local_messages` `m` ON `m`.`account_id` = `a`.`id` WHERE (`a`.`user_id` = 'user12345') AND (`m`.`type` = 0)	32	0
2024-01-10T18:32:27.9695664Z 2024-01-10 18:30:05.931252	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000063	00:00:00.000038	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9696747Z 2024-01-10 18:30:05.978993	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000110	00:00:00.000017	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '8'	32	0
2024-01-10T18:32:27.9698230Z 2024-01-10 18:30:06.017857	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000330	00:00:00.000179	0	1	nextcloud	0	0	1	DELETE FROM `oc_mail_accounts` WHERE (`provisioning_id` IS NOT NULL) AND (`provisioning_id` NOT IN (SELECT `id` FROM `oc_mail_provisionings`))	32	1
2024-01-10T18:32:27.9699516Z 2024-01-10 18:30:06.026036	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000043	00:00:00.000026	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_mailboxes`	32	0
2024-01-10T18:32:27.9700587Z 2024-01-10 18:30:06.033893	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000028	00:00:00.000017	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_mailboxes`	32	0
2024-01-10T18:32:27.9701733Z 2024-01-10 18:30:06.044046	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000191	00:00:00.000019	5	10	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '13'	32	0
2024-01-10T18:32:27.9702793Z 2024-01-10 18:30:06.048888	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000035	00:00:00.000019	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_mailboxes`	32	0
2024-01-10T18:32:27.9703798Z 2024-01-10 18:30:06.053330	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000027	00:00:00.000015	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_mailboxes`	32	0
2024-01-10T18:32:27.9704837Z 2024-01-10 18:30:06.060273	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000030	00:00:00.000016	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_mailboxes`	32	0
2024-01-10T18:32:27.9705788Z 2024-01-10 18:30:06.088345	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000321	00:00:00.000234	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_messages`	32	0
2024-01-10T18:32:27.9707575Z 2024-01-10 18:30:06.101044	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000146	00:00:00.000022	0	20	nextcloud	0	0	1	UPDATE `oc_mail_messages` SET `in_reply_to` = NULL WHERE `in_reply_to` LIKE '<>'	32	10
2024-01-10T18:32:27.9708960Z 2024-01-10 18:30:06.101682	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000105	00:00:00.000017	0	20	nextcloud	0	0	1	SELECT * FROM `oc_mail_messages` WHERE `in_reply_to` LIKE '<>'	32	0
2024-01-10T18:32:27.9710469Z 2024-01-10 18:30:06.510003	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000117	00:00:00.000064	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_messages`	32	0
2024-01-10T18:32:27.9711689Z 2024-01-10 18:30:06.511966	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000091	00:00:00.000038	0	1	nextcloud	0	0	1	UPDATE `oc_mail_messages` SET `structure_analyzed` = 0	32	1
2024-01-10T18:32:27.9712935Z 2024-01-10 18:30:06.513127	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000196	00:00:00.000053	1	1	nextcloud	0	0	1	SELECT COUNT(*) FROM `oc_mail_messages` WHERE (`uid` = 1704911406) AND (`structure_analyzed` = 1)	32	0
2024-01-10T18:32:27.9714239Z 2024-01-10 18:30:06.520277	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000072	00:00:00.000037	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_messages`	32	0
2024-01-10T18:32:27.9716056Z 2024-01-10 18:30:06.539380	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000446	00:00:00.000190	3	9	nextcloud	0	0	1	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`) WHERE (`m`.`mailbox_id` = '1') AND (`m2`.`id` IS NULL) ORDER BY `m`.`sent_at` DESC LIMIT 3	32	0
2024-01-10T18:32:27.9717798Z 2024-01-10 18:30:06.722683	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000126	00:00:00.000020	1	2	nextcloud	0	0	1	SELECT * FROM `oc_mail_provisionings` ORDER BY `provisioning_domain` desc	32	0
2024-01-10T18:32:27.9718927Z 2024-01-10 18:30:06.730281	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000032	00:00:00.000019	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_recipients`	32	0
2024-01-10T18:32:27.9719996Z 2024-01-10 18:30:06.730589	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000032	00:00:00.000021	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9721079Z 2024-01-10 18:30:06.788366	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000233	00:00:00.000027	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '12'	32	0
2024-01-10T18:32:27.9722161Z 2024-01-10 18:30:06.818020	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000045	00:00:00.000024	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_recipients`	32	0
2024-01-10T18:32:27.9723208Z 2024-01-10 18:30:06.818330	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000031	00:00:00.000020	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9724270Z 2024-01-10 18:30:06.900546	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000118	00:00:00.000018	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '13'	32	0
2024-01-10T18:32:27.9725329Z 2024-01-10 18:30:06.927029	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000093	00:00:00.000057	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_recipients`	32	0
2024-01-10T18:32:27.9726381Z 2024-01-10 18:30:06.927527	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000039	00:00:00.000026	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9727545Z 2024-01-10 18:30:07.031408	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000111	00:00:00.000019	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '14'	32	0
2024-01-10T18:32:27.9728656Z 2024-01-10 18:30:07.045661	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000064	00:00:00.000034	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_recipients`	32	0
2024-01-10T18:32:27.9729677Z 2024-01-10 18:30:07.045974	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000036	00:00:00.000023	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9731303Z 2024-01-10 18:30:07.163347	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000119	00:00:00.000022	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '15'	32	0
2024-01-10T18:32:27.9732469Z 2024-01-10 18:30:07.196848	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000063	00:00:00.000031	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_recipients`	32	0
2024-01-10T18:32:27.9733535Z 2024-01-10 18:30:07.197194	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000040	00:00:00.000023	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9735077Z 2024-01-10 18:30:07.303306	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000159	00:00:00.000026	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '16'	32	0
2024-01-10T18:32:27.9736297Z 2024-01-10 18:30:07.349854	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000084	00:00:00.000052	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_recipients`	32	0
2024-01-10T18:32:27.9737370Z 2024-01-10 18:30:07.350195	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000041	00:00:00.000028	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9738459Z 2024-01-10 18:30:07.440040	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000202	00:00:00.000039	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '17'	32	0
2024-01-10T18:32:27.9739700Z 2024-01-10 18:31:40.109685	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000085	00:00:00.000017	4	4	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '18'	32	0
2024-01-10T18:32:27.9740966Z 2024-01-10 18:31:41.052669	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000155	00:00:00.000029	4	8	nextcloud	0	0	1	SELECT * FROM `oc_mail_mailboxes` WHERE `account_id` = '19'	32	0
2024-01-10T18:32:27.9742930Z 2024-01-10 18:31:44.942031	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000290	00:00:00.000096	0	1	nextcloud	0	0	1	SELECT `id`, `subject`, `message_id`, `in_reply_to`, `references`, `thread_root_id` FROM `oc_mail_messages` WHERE (`mailbox_id` IN (SELECT `id` FROM `oc_mail_mailboxes` WHERE `account_id` = 23)) AND (`message_id` IS NOT NULL) AND ((`in_reply_to` IS NOT NULL) OR (`references` <> '[]'))	32	0
2024-01-10T18:32:27.9745578Z 2024-01-10 18:31:47.090090	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000252	00:00:00.000116	0	1	nextcloud	0	0	1	SELECT `id`, `subject`, `message_id`, `in_reply_to`, `references`, `thread_root_id` FROM `oc_mail_messages` WHERE (`mailbox_id` IN (SELECT `id` FROM `oc_mail_mailboxes` WHERE `account_id` = 25)) AND (`message_id` IS NOT NULL) AND ((`in_reply_to` IS NOT NULL) OR (`references` <> '[]'))	32	0
2024-01-10T18:32:27.9748127Z 2024-01-10 18:31:48.429079	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000192	00:00:00.000058	0	1	nextcloud	0	0	1	SELECT `id`, `subject`, `message_id`, `in_reply_to`, `references`, `thread_root_id` FROM `oc_mail_messages` WHERE (`mailbox_id` IN (SELECT `id` FROM `oc_mail_mailboxes` WHERE `account_id` = 26)) AND (`message_id` IS NOT NULL) AND ((`in_reply_to` IS NOT NULL) OR (`references` <> '[]'))	32	0
2024-01-10T18:32:27.9750401Z 2024-01-10 18:31:48.746610	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000099	00:00:00.000031	1	0	nextcloud	0	0	1	SELECT COUNT(*) FROM `oc_mail_recipients` `r` INNER JOIN `oc_mail_messages` `m` ON `m`.`id` = `r`.`message_id` WHERE (`r`.`type` = 0) AND (`m`.`mailbox_id` IN (99))	32	0
2024-01-10T18:32:27.9752604Z 2024-01-10 18:31:48.937137	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000318	00:00:00.000112	0	2	nextcloud	0	0	1	SELECT `id`, `subject`, `message_id`, `in_reply_to`, `references`, `thread_root_id` FROM `oc_mail_messages` WHERE (`mailbox_id` IN (SELECT `id` FROM `oc_mail_mailboxes` WHERE `account_id` = 26)) AND (`message_id` IS NOT NULL) AND ((`in_reply_to` IS NOT NULL) OR (`references` <> '[]'))	32	0
2024-01-10T18:32:27.9755045Z 2024-01-10 18:31:51.341097	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000240	00:00:00.000059	0	4	nextcloud	0	0	1	SELECT `id`, `subject`, `message_id`, `in_reply_to`, `references`, `thread_root_id` FROM `oc_mail_messages` WHERE (`mailbox_id` IN (SELECT `id` FROM `oc_mail_mailboxes` WHERE `account_id` = 27)) AND (`message_id` IS NOT NULL) AND ((`in_reply_to` IS NOT NULL) OR (`references` <> '[]'))	32	0
2024-01-10T18:32:27.9758557Z 2024-01-10 18:31:52.241228	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000183	00:00:00.000044	0	4	nextcloud	0	0	1	SELECT `id`, `subject`, `message_id`, `in_reply_to`, `references`, `thread_root_id` FROM `oc_mail_messages` WHERE (`mailbox_id` IN (SELECT `id` FROM `oc_mail_mailboxes` WHERE `account_id` = 28)) AND (`message_id` IS NOT NULL) AND ((`in_reply_to` IS NOT NULL) OR (`references` <> '[]'))	32	0
2024-01-10T18:32:27.9761057Z 2024-01-10 18:31:52.558773	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000285	00:00:00.000101	0	4	nextcloud	0	0	1	SELECT `id`, `subject`, `message_id`, `in_reply_to`, `references`, `thread_root_id` FROM `oc_mail_messages` WHERE (`mailbox_id` IN (SELECT `id` FROM `oc_mail_mailboxes` WHERE `account_id` = 28)) AND (`message_id` IS NOT NULL) AND ((`in_reply_to` IS NOT NULL) OR (`references` <> '[]'))	32	0
2024-01-10T18:32:27.9763602Z 2024-01-10 18:31:53.094309	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000188	00:00:00.000046	0	4	nextcloud	0	0	1	SELECT `id`, `subject`, `message_id`, `in_reply_to`, `references`, `thread_root_id` FROM `oc_mail_messages` WHERE (`mailbox_id` IN (SELECT `id` FROM `oc_mail_mailboxes` WHERE `account_id` = 29)) AND (`message_id` IS NOT NULL) AND ((`in_reply_to` IS NOT NULL) OR (`references` <> '[]'))	32	0
2024-01-10T18:32:27.9765449Z 2024-01-10 18:31:56.403112	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000079	00:00:00.000051	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9766606Z 2024-01-10 18:31:58.446549	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.004490	00:00:00.000039	0	1	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	1
2024-01-10T18:32:27.9767681Z 2024-01-10 18:31:59.545872	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000087	00:00:00.000056	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9768832Z 2024-01-10 18:32:00.601462	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.012020	00:00:00.000053	0	1	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	1
2024-01-10T18:32:27.9769915Z 2024-01-10 18:32:01.527008	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.002802	00:00:00.000034	0	1	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	1
2024-01-10T18:32:27.9771707Z 2024-01-10 18:32:07.961111	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000289	00:00:00.000084	0	4	nextcloud	0	0	1	SELECT `messages`.* FROM `oc_mail_messages` `messages` INNER JOIN `oc_mail_mailboxes` `mailboxes` ON `messages`.`mailbox_id` = `mailboxes`.`id` WHERE (`mailboxes`.`account_id` = 38) AND (`messages`.`message_id` = '<message@server>')	32	0
2024-01-10T18:32:27.9773882Z 2024-01-10 18:32:09.695827	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000233	00:00:00.000073	0	4	nextcloud	0	0	1	SELECT `messages`.* FROM `oc_mail_messages` `messages` INNER JOIN `oc_mail_mailboxes` `mailboxes` ON `messages`.`mailbox_id` = `mailboxes`.`id` WHERE (`mailboxes`.`account_id` = 39) AND (`messages`.`message_id` = '<message@server>')	32	0
2024-01-10T18:32:27.9776057Z 2024-01-10 18:32:11.552442	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000365	00:00:00.000149	0	4	nextcloud	0	0	1	SELECT `messages`.* FROM `oc_mail_messages` `messages` INNER JOIN `oc_mail_mailboxes` `mailboxes` ON `messages`.`mailbox_id` = `mailboxes`.`id` WHERE (`mailboxes`.`account_id` = 40) AND (`messages`.`message_id` = '<message@server>')	32	0
2024-01-10T18:32:27.9777667Z 2024-01-10 18:32:16.375122	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000090	00:00:00.000052	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9778719Z 2024-01-10 18:32:17.880482	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.004832	00:00:00.000059	0	1	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	1
2024-01-10T18:32:27.9780188Z 2024-01-10 18:32:17.897074	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000474	00:00:00.000122	2	1	nextcloud	0	0	1	SELECT `m`.* FROM `oc_mail_accounts` `a` INNER JOIN `oc_mail_local_messages` `m` ON `m`.`account_id` = `a`.`id` WHERE (`a`.`user_id` = 'testuser392583379686314933') AND (`m`.`type` = 0)	32	0
2024-01-10T18:32:27.9782313Z 2024-01-10 18:32:18.850277	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.018838	00:00:00.000061	0	2	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	2
2024-01-10T18:32:27.9784617Z 2024-01-10 18:32:19.236170	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000476	00:00:00.000139	0	4	nextcloud	0	0	1	SELECT `id`, `subject`, `message_id`, `in_reply_to`, `references`, `thread_root_id` FROM `oc_mail_messages` WHERE (`mailbox_id` IN (SELECT `id` FROM `oc_mail_mailboxes` WHERE `account_id` = 46)) AND (`message_id` IS NOT NULL) AND ((`in_reply_to` IS NOT NULL) OR (`references` <> '[]'))	32	0
2024-01-10T18:32:27.9786818Z 2024-01-10 18:32:19.661417	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000132	00:00:00.000040	1	1	nextcloud	0	0	1	SELECT `m`.* FROM `oc_mail_accounts` `a` INNER JOIN `oc_mail_local_messages` `m` ON `m`.`account_id` = `a`.`id` WHERE (`a`.`user_id` = 'testuser8993083895866261238') AND (`m`.`type` = 0)	32	0
2024-01-10T18:32:27.9788423Z 2024-01-10 18:32:20.755832	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.008344	00:00:00.000087	0	1	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	1
2024-01-10T18:32:27.9789930Z 2024-01-10 18:32:21.094700	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000127	00:00:00.000039	1	1	nextcloud	0	0	1	SELECT `m`.* FROM `oc_mail_accounts` `a` INNER JOIN `oc_mail_local_messages` `m` ON `m`.`account_id` = `a`.`id` WHERE (`a`.`user_id` = 'testuser5120471712663910443') AND (`m`.`type` = 0)	32	0
2024-01-10T18:32:27.9791403Z 2024-01-10 18:32:22.300463	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.002392	00:00:00.000063	0	1	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	1
2024-01-10T18:32:27.9792462Z 2024-01-10 18:32:23.182796	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000106	00:00:00.000059	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9793526Z 2024-01-10 18:32:24.136307	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.010656	00:00:00.000050	0	1	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	1
2024-01-10T18:32:27.9794589Z 2024-01-10 18:32:25.174843	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000102	00:00:00.000054	0	0	nextcloud	0	0	1	DELETE FROM `oc_mail_local_messages`	32	0
2024-01-10T18:32:27.9796086Z 2024-01-10 18:32:25.180635	nextcloud[nextcloud] @  [172.18.0.1]	00:00:00.000160	00:00:00.000038	1	2	nextcloud	0	0	1	SELECT * FROM `oc_mail_local_messages` WHERE (`send_at` IS NOT NULL) AND (`type` = 0) AND (`send_at` <= 1704911545) AND ((`failed` IS NULL) OR (`failed` = 0)) ORDER BY `send_at` asc	32	0

Ref https://github.com/nextcloud/mail/blob/a2b5a629376f0a6ee5e3ade48b341504ec659213/.github/workflows/test.yml#L168-L171 Ref https://github.com/nextcloud/mail/blob/a2b5a629376f0a6ee5e3ade48b341504ec659213/.github/workflows/test.yml#L184-L186

ChristophWurst avatar Jan 11 '24 10:01 ChristophWurst

From analyzing tables of a production system: oc_mail_classifiers is the 4th largest table in terms of rows. This can't be right. We only need one classifier per account. The rest is history data that can go.

https://github.com/nextcloud/mail/pull/9001 should take care of that.

ChristophWurst avatar Jan 11 '24 16:01 ChristophWurst

From slow query log and pt-query-digest:

# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      40
# Exec time      1      2s    12ms   158ms    43ms   105ms    36ms    27ms
# Lock time      0     4ms    55us   288us    91us   159us    41us    76us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine  18 939.46k  23.48k  23.49k  23.49k  22.45k    0.00  22.45k
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     0  93.32k   2.33k   2.33k   2.33k   2.33k       0   2.33k
# Query size     0   9.79k     201     300  250.50  299.03   49.50  299.03

# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  #########
#    1s
#  10s+


MariaDB [nextcloud]> SELECT /*!40001 SQL_NO_CACHE */ * FROM `oc_mail_messages` WHERE (`sent_at` <= 1703814013) AND (`structure_analyzed` = 0) AND (`mailbox_id` IN (10, 1, 58, 14, 24, 77, 51, 21, 54, 23, 46, 55, 19, 18, 8, 53, 6, 26, 12, 75, 25, 5, 49, 76, 20, 2, 52, 11, 9, 57, 7, 50, 22, 48, 17, 59, 16, 47, 13, 56, 3, 4)) ORDER BY `sent_at` ASC;
Empty set (0,014 sec)
MariaDB [nextcloud]> EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `oc_mail_messages` WHERE (`sent_at` <= 1703814013) AND (`structure_analyzed` = 0) AND (`mailbox_id` IN (10, 1, 58, 14, 24, 77, 51, 21, 54, 23, 46, 55, 19, 18, 8, 53, 6, 26, 12, 75, 25, 5, 49, 76, 20, 2, 52, 11, 9, 57, 7, 50, 22, 48, 17, 59, 16, 47, 13, 56, 3, 4)) ORDER BY `sent_at` ASC;
+------+-------------+------------------+------+----------------------------------------------------------------------------------------------------------------------------+------+---------+------+-------+-----------------------------+
| id   | select_type | table            | type | possible_keys                                                                                                              | key  | key_len | ref  | rows  | Extra                       |
+------+-------------+------------------+------+----------------------------------------------------------------------------------------------------------------------------+------+---------+------+-------+-----------------------------+
|    1 | SIMPLE      | oc_mail_messages | ALL  | mail_messages_id_flags,mail_messages_id_flags2,mail_messages_mailbox_id,mail_msg_thrd_root_snt_idx,mail_messages_mb_id_uid | NULL | NULL    | NULL | 23250 | Using where; Using filesort |
+------+-------------+------------------+------+----------------------------------------------------------------------------------------------------------------------------+------+---------+------+-------+-----------------------------+

No index used. Possibly due to the large IN clause. To check if an index could help, or restructuring to a join or sub query.

ChristophWurst avatar Jan 12 '24 07:01 ChristophWurst

An idea for avoiding the self-join of the messages table for mailbox listing:

EXPLAIN SELECT `m`.`id`, `m`.`sent_at`, m.subject 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`) WHERE (`m`.`mailbox_id` = 7876) AND (`m`.`flag_deleted` = 0) AND (`m2`.`id` IS NULL) ORDER BY `sent_at` DESC LIMIT 20


EXPLAIN SELECT id, min(sent_at), thread_root_id, subject FROM oc_mail_messages where mailbox_id = 7876 group by thread_root_id order by max(sent_at) desc limit 20;
or
EXPLAIN SELECT id, subject FROM oc_mail_messages WHERE mailbox_id = 7876 and thread_root_id IN (SELECT thread_root_id FROM oc_mail_messages where mailbox_id = 7876 group by thread_root_id, thread_root_id order by max(sent_at) desc) limit 20;

before/after

Would be interesting to test the querys on a large db. On a table with some thousand the self-join is much faster than the min(), max() option.

JohannesGGE avatar Jan 23 '24 11:01 JohannesGGE

Some improvements could be:

oc_mail_messages

  • add index on structure_analyzed
  • drop index mail_messages_mailbox_id because mail_messages_mb_id_uid already contains mailbox_id on first position
  • make mail_messages_mb_id_uid unique. Should be possible

oc_mail_classifiers

  • maybe add index on created_at. Will probably improve the queries in \OCA\Mail\Db\ClassifierMapper

oc_mail_accounts

  • add index on provisioning_id

oc_mail_smime_certificates

  • extend mail_smime_certs_uid_idx to cover (user_id,email_address)

oc_mail_tags

  • drop mail_msg_tags_usr_id_index because mail_msg_tags_usr_lbl_idx already contains user_id on first position

oc_mail_aliases

  • add index on account_id because it's joint in this

oc_mail_trusted_senders

  • add index on (user_id,email,type) and drop mail_trusted_senders_type

oc_mail_coll_addresses

  • add index on (user_id, email, display_name) and drop mail_coll_addr_userid_index and mail_coll_addr_email_index

JohannesGGE avatar Jan 24 '24 16:01 JohannesGGE

Manual analysis above confirmed by mariadb-sys:

MariaDB [(none)]> select * from sys.schema_redundant_indexes sri where table_schema = 'nextclouddev' and table_name like 'oc_mail_%';
+--------------+----------------------------+-----------------------------+-------------------------+----------------------------+----------------------------+--------------------------------------------------+---------------------------+----------------+--------------------------------------------------------------------------------------------------+
| table_schema | table_name                 | redundant_index_name        | redundant_index_columns | redundant_index_non_unique | dominant_index_name        | dominant_index_columns                           | dominant_index_non_unique | subpart_exists | sql_drop_index                                                                                   |
+--------------+----------------------------+-----------------------------+-------------------------+----------------------------+----------------------------+--------------------------------------------------+---------------------------+----------------+--------------------------------------------------------------------------------------------------+
| nextclouddev | oc_mail_messages           | mail_messages_mailbox_id    | mailbox_id              |                          1 | mail_messages_id_flags     | mailbox_id,flag_important,flag_deleted,flag_seen |                         1 |              0 | ALTER TABLE `nextclouddev`.`oc_mail_messages` DROP INDEX `mail_messages_mailbox_id`              |
| nextclouddev | oc_mail_messages           | mail_messages_mailbox_id    | mailbox_id              |                          1 | mail_messages_id_flags2    | mailbox_id,flag_deleted,flag_flagged             |                         1 |              0 | ALTER TABLE `nextclouddev`.`oc_mail_messages` DROP INDEX `mail_messages_mailbox_id`              |
| nextclouddev | oc_mail_messages           | mail_messages_mailbox_id    | mailbox_id              |                          1 | mail_messages_mb_id_uid    | mailbox_id,uid                                   |                         1 |              0 | ALTER TABLE `nextclouddev`.`oc_mail_messages` DROP INDEX `mail_messages_mailbox_id`              |
| nextclouddev | oc_mail_messages           | mail_messages_mailbox_id    | mailbox_id              |                          1 | mail_msg_thrd_root_snt_idx | mailbox_id,thread_root_id,sent_at                |                         1 |              1 | ALTER TABLE `nextclouddev`.`oc_mail_messages` DROP INDEX `mail_messages_mailbox_id`              |
| nextclouddev | oc_mail_smime_certificates | mail_smime_certs_id_uid_idx | id,user_id              |                          1 | PRIMARY                    | id                                               |                         0 |              0 | ALTER TABLE `nextclouddev`.`oc_mail_smime_certificates` DROP INDEX `mail_smime_certs_id_uid_idx` |
| nextclouddev | oc_mail_tags               | mail_msg_tags_usr_id_index  | user_id                 |                          1 | mail_msg_tags_usr_lbl_idx  | user_id,imap_label                               |                         0 |              0 | ALTER TABLE `nextclouddev`.`oc_mail_tags` DROP INDEX `mail_msg_tags_usr_id_index`                |
+--------------+----------------------------+-----------------------------+-------------------------+----------------------------+----------------------------+--------------------------------------------------+---------------------------+----------------+--------------------------------------------------------------------------------------------------+
6 rows in set (0,016 sec)

mail_smime_certs_id_uid_idx is interesting. We only have the index for the purpose of a unique constraint.

ChristophWurst avatar Jan 25 '24 09:01 ChristophWurst