SQL: Characters db storage type cleanup #1 - Note:

- MySQL numeric types can NOT be altered in value range or or storage size at all, so things like INT(32) are entirely pointless. As TC currently doesn't use the display width of numeric types, use the default width to avoid confusion. (see MySQL numeric types docs)

- Timestamps can be stored as INT(10) UNSIGNED. As the max value of this type is 4294967295 which translates into year 2106 using it as timestamp we are NOT affected by the year 2038 bug. If the timestamp needs to be negative in some cases, i.e. for displaying infinity using -1, use BIGINT(20) instead.

- Do NOT set ROW_FORMAT for InnoDB tables unless you specifically want COMPRESSED tables (which we don't for performance reasons). MySQL will chose the appropriate ROW_FORMAT by itself depending on the innodb_file_format setting of the server. (FIXED is only available for MyISAM)

- Even though VARCHAR does require less storage space than CHAR for values with variable length, the length still needs to be chosen wisely as this doesn't apply to memory consumption.

11 files changed, 78 insertions, 32 deletions

diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql
index 9e923ab66ed..9fb176d16d2 100644
--- a/sql/base/characters_database.sql
+++ b/sql/base/characters_database.sql
@@ -23,10 +23,10 @@ DROP TABLE IF EXISTS `account_data`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `account_data` (
-  `account` int(11) unsigned NOT NULL DEFAULT '0',
-  `type` int(11) unsigned NOT NULL DEFAULT '0',
-  `time` bigint(11) unsigned NOT NULL DEFAULT '0',
-  `data` longblob NOT NULL,
+  `account` int(10) unsigned NOT NULL DEFAULT '0',
+  `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
+  `time` int(10) unsigned NOT NULL DEFAULT '0',
+  `data` blob NOT NULL,
   PRIMARY KEY (`account`,`type`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
@@ -49,7 +49,7 @@ DROP TABLE IF EXISTS `addons`;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `addons` (
   `name` varchar(120) NOT NULL DEFAULT '',
-  `crc` int(32) unsigned NOT NULL DEFAULT '0',
+  `crc` int(10) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Addons';
 /*!40101 SET character_set_client = @saved_cs_client */;
@@ -72,13 +72,13 @@ DROP TABLE IF EXISTS `arena_team`;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `arena_team` (
   `arenateamid` int(10) unsigned NOT NULL DEFAULT '0',
-  `name` char(255) NOT NULL,
+  `name` varchar(24) NOT NULL,
   `captainguid` int(10) unsigned NOT NULL DEFAULT '0',
   `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
   `BackgroundColor` int(10) unsigned NOT NULL DEFAULT '0',
-  `EmblemStyle` int(10) unsigned NOT NULL DEFAULT '0',
+  `EmblemStyle` tinyint(3) unsigned NOT NULL DEFAULT '0',
   `EmblemColor` int(10) unsigned NOT NULL DEFAULT '0',
-  `BorderStyle` int(10) unsigned NOT NULL DEFAULT '0',
+  `BorderStyle` tinyint(3) unsigned NOT NULL DEFAULT '0',
   `BorderColor` int(10) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`arenateamid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -156,16 +156,16 @@ DROP TABLE IF EXISTS `auctionhouse`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `auctionhouse` (
-  `id` int(11) unsigned NOT NULL DEFAULT '0',
-  `auctioneerguid` int(11) unsigned NOT NULL DEFAULT '0',
-  `itemguid` int(11) unsigned NOT NULL DEFAULT '0',
-  `itemowner` int(11) unsigned NOT NULL DEFAULT '0',
-  `buyoutprice` int(11) NOT NULL DEFAULT '0',
-  `time` bigint(40) NOT NULL DEFAULT '0',
-  `buyguid` int(11) unsigned NOT NULL DEFAULT '0',
-  `lastbid` int(11) NOT NULL DEFAULT '0',
-  `startbid` int(11) NOT NULL DEFAULT '0',
-  `deposit` int(11) NOT NULL DEFAULT '0',
+  `id` int(10) unsigned NOT NULL DEFAULT '0',
+  `auctioneerguid` int(10) unsigned NOT NULL DEFAULT '0',
+  `itemguid` int(10) unsigned NOT NULL DEFAULT '0',
+  `itemowner` int(10) unsigned NOT NULL DEFAULT '0',
+  `buyoutprice` int(10) unsigned NOT NULL DEFAULT '0',
+  `time` int(10) unsigned NOT NULL DEFAULT '0',
+  `buyguid` int(10) unsigned NOT NULL DEFAULT '0',
+  `lastbid` int(10) unsigned NOT NULL DEFAULT '0',
+  `startbid` int(10) unsigned NOT NULL DEFAULT '0',
+  `deposit` int(10) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   UNIQUE KEY `item_guid` (`itemguid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -188,7 +188,7 @@ DROP TABLE IF EXISTS `bugreport`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `bugreport` (
-  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
+  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
   `type` longtext NOT NULL,
   `content` longtext NOT NULL,
   PRIMARY KEY (`id`)
@@ -217,7 +217,7 @@ CREATE TABLE `channels` (
   `m_announce` tinyint(3) unsigned NOT NULL DEFAULT '1',
   `m_ownership` tinyint(3) unsigned NOT NULL DEFAULT '1',
   `m_password` varchar(32) DEFAULT NULL,
-  `BannedList` longtext,
+  `BannedList` text,
   `last_used` int(10) unsigned NOT NULL,
   PRIMARY KEY (`m_name`,`m_team`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Channel System';
@@ -240,10 +240,10 @@ DROP TABLE IF EXISTS `character_account_data`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `character_account_data` (
-  `guid` int(11) unsigned NOT NULL DEFAULT '0',
-  `type` int(11) unsigned NOT NULL DEFAULT '0',
-  `time` bigint(11) unsigned NOT NULL DEFAULT '0',
-  `data` longblob NOT NULL,
+  `guid` int(10) unsigned NOT NULL DEFAULT '0',
+  `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
+  `time` int(10) unsigned NOT NULL DEFAULT '0',
+  `data` blob NOT NULL,
   PRIMARY KEY (`guid`,`type`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
@@ -265,9 +265,9 @@ DROP TABLE IF EXISTS `character_achievement`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `character_achievement` (
-  `guid` int(11) unsigned NOT NULL,
-  `achievement` int(11) unsigned NOT NULL,
-  `date` bigint(11) unsigned NOT NULL DEFAULT '0',
+  `guid` int(10) unsigned NOT NULL,
+  `achievement` smallint(5) unsigned NOT NULL,
+  `date` int(10) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`guid`,`achievement`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
@@ -289,10 +289,10 @@ DROP TABLE IF EXISTS `character_achievement_progress`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `character_achievement_progress` (
-  `guid` int(11) unsigned NOT NULL,
-  `criteria` int(11) unsigned NOT NULL,
-  `counter` int(11) unsigned NOT NULL,
-  `date` bigint(11) unsigned NOT NULL DEFAULT '0',
+  `guid` int(10) unsigned NOT NULL,
+  `criteria` smallint(5) unsigned NOT NULL,
+  `counter` int(10) unsigned NOT NULL,
+  `date` int(10) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`guid`,`criteria`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
@@ -2198,4 +2198,4 @@ UNLOCK TABLES;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2011-01-19  3:23:11
+-- Dump completed on 2011-01-19  4:18:09
diff --git a/sql/updates/2011_01_19_0_characters_account_data.sql b/sql/updates/2011_01_19_0_characters_account_data.sql
new file mode 100644
index 00000000000..26e465268d6
--- /dev/null
+++ b/sql/updates/2011_01_19_0_characters_account_data.sql
@@ -0,0 +1,5 @@
+ALTER TABLE `account_data` 
+CHANGE `account` `account` INT(10) UNSIGNED DEFAULT '0' NOT NULL, 
+CHANGE `type` `type` TINYINT(3) UNSIGNED DEFAULT '0' NOT NULL, 
+CHANGE `time` `time` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `data` `data` BLOB NOT NULL;
\ No newline at end of file
diff --git a/sql/updates/2011_01_19_0_characters_addons.sql b/sql/updates/2011_01_19_0_characters_addons.sql
new file mode 100644
index 00000000000..90ebbce5613
--- /dev/null
+++ b/sql/updates/2011_01_19_0_characters_addons.sql
@@ -0,0 +1,3 @@
+ALTER TABLE `addons` 
+ROW_FORMAT=DEFAULT,
+CHANGE `crc` `crc` INT(10) UNSIGNED DEFAULT '0' NOT NULL;
\ No newline at end of file
diff --git a/sql/updates/2011_01_19_0_characters_arena_team.sql b/sql/updates/2011_01_19_0_characters_arena_team.sql
new file mode 100644
index 00000000000..5942e008bb5
--- /dev/null
+++ b/sql/updates/2011_01_19_0_characters_arena_team.sql
@@ -0,0 +1,4 @@
+ALTER TABLE `arena_team` 
+CHANGE `name` `name` VARCHAR(24) NOT NULL,
+CHANGE `EmblemStyle` `EmblemStyle` TINYINT(3) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `BorderStyle` `BorderStyle` TINYINT(3) UNSIGNED DEFAULT '0' NOT NULL;
\ No newline at end of file
diff --git a/sql/updates/2011_01_19_0_characters_auctionhouse.sql b/sql/updates/2011_01_19_0_characters_auctionhouse.sql
new file mode 100644
index 00000000000..20719a2ca61
--- /dev/null
+++ b/sql/updates/2011_01_19_0_characters_auctionhouse.sql
@@ -0,0 +1,11 @@
+ALTER TABLE `auctionhouse`
+CHANGE `id` `id` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `auctioneerguid` `auctioneerguid` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `itemguid` `itemguid` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `itemowner` `itemowner` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `buyoutprice` `buyoutprice` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `time` `time` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `buyguid` `buyguid` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `lastbid` `lastbid` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `startbid` `startbid` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `deposit` `deposit` INT(10) UNSIGNED DEFAULT '0' NOT NULL;
\ No newline at end of file
diff --git a/sql/updates/2011_01_19_0_characters_bug_report.sql b/sql/updates/2011_01_19_0_characters_bug_report.sql
new file mode 100644
index 00000000000..e9c40877e69
--- /dev/null
+++ b/sql/updates/2011_01_19_0_characters_bug_report.sql
@@ -0,0 +1 @@
+ALTER TABLE `addons` ROW_FORMAT=DEFAULT;
\ No newline at end of file
diff --git a/sql/updates/2011_01_19_0_characters_bugreport.sql b/sql/updates/2011_01_19_0_characters_bugreport.sql
new file mode 100644
index 00000000000..1ceb0abe429
--- /dev/null
+++ b/sql/updates/2011_01_19_0_characters_bugreport.sql
@@ -0,0 +1,3 @@
+ALTER TABLE `bugreport`
+ROW_FORMAT=DEFAULT,
+CHANGE `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Identifier';
\ No newline at end of file
diff --git a/sql/updates/2011_01_19_0_characters_channels.sql b/sql/updates/2011_01_19_0_characters_channels.sql
new file mode 100644
index 00000000000..cd70f39e30f
--- /dev/null
+++ b/sql/updates/2011_01_19_0_characters_channels.sql
@@ -0,0 +1,5 @@
+ALTER TABLE `channels`
+ROW_FORMAT=DEFAULT,
+CHANGE `BannedList` `BannedList` TEXT,
+DROP PRIMARY KEY, 
+ADD PRIMARY KEY (`m_name`, `m_team`);
\ No newline at end of file
diff --git a/sql/updates/2011_01_19_0_characters_character_account_data.sql b/sql/updates/2011_01_19_0_characters_character_account_data.sql
new file mode 100644
index 00000000000..bf5898266d7
--- /dev/null
+++ b/sql/updates/2011_01_19_0_characters_character_account_data.sql
@@ -0,0 +1,5 @@
+ALTER TABLE `character_account_data`
+CHANGE `guid` `guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `type` `type` TINYINT(3) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `time` `time` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
+CHANGE `data` `data` BLOB NOT NULL;
\ No newline at end of file
diff --git a/sql/updates/2011_01_19_0_characters_character_achievement.sql b/sql/updates/2011_01_19_0_characters_character_achievement.sql
new file mode 100644
index 00000000000..cbfc6e95aa8
--- /dev/null
+++ b/sql/updates/2011_01_19_0_characters_character_achievement.sql
@@ -0,0 +1,4 @@
+ALTER TABLE `character_achievement`
+CHANGE `guid` `guid` INT(10) UNSIGNED NOT NULL,
+CHANGE `achievement` `achievement` SMALLINT(5) UNSIGNED NOT NULL,
+CHANGE `date` `date` INT(10) UNSIGNED DEFAULT '0' NOT NULL;
\ No newline at end of file
diff --git a/sql/updates/2011_01_19_0_characters_character_achievement_progress.sql b/sql/updates/2011_01_19_0_characters_character_achievement_progress.sql
new file mode 100644
index 00000000000..ba91258f82d
--- /dev/null
+++ b/sql/updates/2011_01_19_0_characters_character_achievement_progress.sql
@@ -0,0 +1,5 @@
+ALTER TABLE `character_achievement_progress`
+CHANGE `guid` `guid` INT(10) UNSIGNED NOT NULL,
+CHANGE `criteria` `criteria` SMALLINT(5) UNSIGNED NOT NULL,
+CHANGE `counter` `counter` INT(10) UNSIGNED NOT NULL,
+CHANGE `date` `date` INT(10) UNSIGNED DEFAULT '0' NOT NULL;
\ No newline at end of file