DB/Schema: Correct use of indexes on mail_items table. Prevents deadlocks in operations on this table.

1 files changed, 7 insertions, 0 deletions

diff --git a/sql/updates/characters/2011_05_29_0_characters_mail_items.sql b/sql/updates/characters/2011_05_29_0_characters_mail_items.sql
new file mode 100644
index 00000000000..6d9feb2401d
--- /dev/null
+++ b/sql/updates/characters/2011_05_29_0_characters_mail_items.sql
@@ -0,0 +1,7 @@
+-- Uncomment this query if the second query gives you primary key violation errors
+-- DELETE FROM `mail_items` WHERE `item_guid` IN (SELECT `item_guid` FROM `mail_items` GROUP BY `item_guid` HAVING COUNT(`item_guid`) > 1);
+
+ALTER TABLE `mail_items`
+DROP PRIMARY KEY,
+ADD PRIMARY KEY(`item_guid`),
+ADD KEY `idx_mail_id` (`mail_id`);