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

2 files changed, 10 insertions, 2 deletions

diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql
index 0630b748692..a85383bc7fd 100644
--- a/sql/base/characters_database.sql
+++ b/sql/base/characters_database.sql
@@ -1966,8 +1966,9 @@ CREATE TABLE `mail_items` (
   `mail_id` int(10) unsigned NOT NULL DEFAULT '0',
   `item_guid` int(10) unsigned NOT NULL DEFAULT '0',
   `receiver` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Character Global Unique Identifier',
-  PRIMARY KEY (`mail_id`,`item_guid`),
-  KEY `idx_receiver` (`receiver`)
+  PRIMARY KEY (`item_guid`),
+  KEY `idx_receiver` (`receiver`),
+  KEY `idx_mail_id` (`mail_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
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`);