v3.5+ DB performance analysis #9162

ChristophWurst · 2023-12-12T09:17:57Z

Steps to reproduce

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

The text was updated successfully, but these errors were encountered:

ChristophWurst · 2024-01-04T17:47:37Z

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 · 2024-01-11T10:25:50Z

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

mail/.github/workflows/test.yml

Lines 168 to 171 in a2b5a62

    
                       run: | 
        
                         echo "SET GLOBAL log_queries_not_using_indexes = 1;" | mysql -h 127.0.0.1 -u root -pmy-secret-pw 
        
                         echo "SET GLOBAL slow_query_log=1;" | mysql -h 127.0.0.1 -u root -pmy-secret-pw 
        
                         echo "SET GLOBAL log_output = 'table';" | mysql -h 127.0.0.1 -u root -pmy-secret-pw

Ref

mail/.github/workflows/test.yml

Lines 184 to 186 in a2b5a62

    
           - name: Read slow queries 
        
             if: ${{ always() }} 
        
             run: echo "SELECT * FROM mysql.slow_log WHERE sql_text LIKE '%oc_mail%' AND sql_text NOT LIKE '%information_schema%'" | mysql -h 127.0.0.1 -u root -pmy-secret-pw

ChristophWurst · 2024-01-11T16:23:28Z

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.

#9001 should take care of that.

ChristophWurst · 2024-01-12T07:39:38Z

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.

JohannesGGE · 2024-01-23T11:14:34Z

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 · 2024-01-24T16:16:29Z

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

ChristophWurst · 2024-01-25T09:19:58Z

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 added bug 0. to triage performance 🚀 labels Dec 12, 2023

ChristophWurst added this to 💌 📅 👥 Groupware team Dec 12, 2023

ChristophWurst moved this to 🧭 Planning evaluation (dont pick) in 💌 📅 👥 Groupware team Dec 12, 2023

ChristophWurst changed the title ~~DB performance analysis~~ v3.5+ DB performance analysis Dec 12, 2023

ChristophWurst removed the bug label Dec 12, 2023

ChristophWurst assigned JohannesGGE Dec 21, 2023

ChristophWurst added 1. to develop and removed 0. to triage labels Dec 21, 2023

ChristophWurst self-assigned this Dec 21, 2023

ChristophWurst moved this from 🧭 Planning evaluation (dont pick) to 📄 To do (~10 entries) in 💌 📅 👥 Groupware team Dec 21, 2023

JohannesGGE moved this from 📄 To do to 🏗️ In progress in 💌 📅 👥 Groupware team Jan 15, 2024

ChristophWurst added 2. developing and removed 1. to develop labels Jan 15, 2024

This was referenced Jan 30, 2024

feat(occ): add possibility to edit indices nextcloud/server#43209

Merged

perf(db): optimise indices of mail tables #9295

Merged

ChristophWurst added 3. to review and removed 2. developing labels Feb 27, 2024

ChristophWurst closed this as completed in #9295 Feb 28, 2024

github-project-automation bot moved this from 🏗️ In progress to ☑️ Done in 💌 📅 👥 Groupware team Feb 28, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

v3.5+ DB performance analysis #9162

v3.5+ DB performance analysis #9162

ChristophWurst commented Dec 12, 2023 •

edited by JohannesGGE

Loading

ChristophWurst commented Jan 4, 2024 •

edited

Loading

ChristophWurst commented Jan 11, 2024

ChristophWurst commented Jan 11, 2024 •

edited

Loading

ChristophWurst commented Jan 12, 2024 •

edited

Loading

JohannesGGE commented Jan 23, 2024

JohannesGGE commented Jan 24, 2024

ChristophWurst commented Jan 25, 2024

v3.5+ DB performance analysis #9162

v3.5+ DB performance analysis #9162

Comments

ChristophWurst commented Dec 12, 2023 • edited by JohannesGGE Loading

Steps to reproduce

Expected behavior

Actual behavior

Mail app version

Mailserver or service

Operating system

PHP engine version

Web server

Database

Additional info

Work packages

ChristophWurst commented Jan 4, 2024 • edited Loading

ChristophWurst commented Jan 11, 2024

ChristophWurst commented Jan 11, 2024 • edited Loading

ChristophWurst commented Jan 12, 2024 • edited Loading

JohannesGGE commented Jan 23, 2024

JohannesGGE commented Jan 24, 2024

oc_mail_messages

oc_mail_classifiers

oc_mail_accounts

oc_mail_smime_certificates

oc_mail_tags

oc_mail_aliases

oc_mail_trusted_senders

oc_mail_coll_addresses

ChristophWurst commented Jan 25, 2024

ChristophWurst commented Dec 12, 2023 •

edited by JohannesGGE

Loading

ChristophWurst commented Jan 4, 2024 •

edited

Loading

ChristophWurst commented Jan 11, 2024 •

edited

Loading

ChristophWurst commented Jan 12, 2024 •

edited

Loading