Add messages table index for mailbox id + uid search

The message update query looks up rows by the mailbox id and the uid.
There was only an index for the mailbox id, so the database still had to
scan data for the matching UID.

On a dev env with ~700k messages this brought down UPDATE queries from
200ms to 5ms. On a production system running v1.13.0 Beta 1 deadlocks
happened before and are now gone.

Signed-off-by: Christoph Wurst <christoph@winzerhof-wurst.at>

1 files changed, 51 insertions, 0 deletions

diff --git a/lib/Migration/Version1130Date20220520062301.php b/lib/Migration/Version1130Date20220520062301.php
new file mode 100644
index 000000000..e54a6ce57
--- /dev/null
+++ b/lib/Migration/Version1130Date20220520062301.php
@@ -0,0 +1,51 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * @author Christoph Wurst <christoph@winzerhof-wurst.at>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Mail\Migration;
+
+use Closure;
+use OCP\DB\ISchemaWrapper;
+use OCP\Migration\IOutput;
+use OCP\Migration\SimpleMigrationStep;
+
+class Version1130Date20220520062301 extends SimpleMigrationStep {
+
+	/**
+	 * @param IOutput $output
+	 * @param Closure $schemaClosure The `\Closure` returns a `ISchemaWrapper`
+	 * @param array $options
+	 * @return null|ISchemaWrapper
+	 */
+	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {
+		/** @var ISchemaWrapper $schema */
+		$schema = $schemaClosure();
+
+		$messagesTable = $schema->getTable('mail_messages');
+		if (!$messagesTable->hasIndex('mail_messages_mb_id_uid')) {
+			$messagesTable->addIndex(['mailbox_id', 'uid'], 'mail_messages_mb_id_uid');
+		}
+
+		return $schema;
+	}
+}