From b150172521636fa5646489688e53d03f0773b9be Mon Sep 17 00:00:00 2001 From: linencloth Date: Sun, 26 Dec 2010 04:16:18 +0100 Subject: Core/QuestStatus: - Separate rewarded quests from active quests, and store them in a new table to reduce database size - Drop the no longer needed `rewarded` column from character_queststatus for smaller table size - Prevent filling the database with dropped quests - Delete useless records - Implement queststatus save "queues" instead of states - Minor optimizations WARNING: Backup your database! --HG-- branch : trunk --- sql/base/characters_database.sql | 9 ++++++++- sql/updates/10828_characters_character_queststatus.sql | 13 +++++++++++++ 2 files changed, 21 insertions(+), 1 deletion(-) create mode 100644 sql/updates/10828_characters_character_queststatus.sql (limited to 'sql') diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql index 85b87c1af05..bfbed7ea722 100644 --- a/sql/base/characters_database.sql +++ b/sql/base/characters_database.sql @@ -857,7 +857,6 @@ CREATE TABLE `character_queststatus` ( `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier', `quest` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Quest Identifier', `status` tinyint(1) unsigned NOT NULL DEFAULT '0', - `rewarded` tinyint(1) unsigned NOT NULL DEFAULT '0', `explored` tinyint(1) unsigned NOT NULL DEFAULT '0', `timer` bigint(20) unsigned NOT NULL DEFAULT '0', `mobcount1` smallint(3) unsigned NOT NULL DEFAULT '0', @@ -906,6 +905,14 @@ LOCK TABLES `character_queststatus_daily` WRITE; /*!40000 ALTER TABLE `character_queststatus_daily` ENABLE KEYS */; UNLOCK TABLES; +DROP TABLE IF EXISTS `character_queststatus_rewarded`; +CREATE TABLE `character_queststatus_rewarded` ( + `guid` int(10) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier', + `quest` int(10) unsigned NOT NULL default '0' COMMENT 'Quest Identifier', + PRIMARY KEY (`guid`,`quest`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Player System'; + + -- -- Table structure for table `character_queststatus_weekly` -- diff --git a/sql/updates/10828_characters_character_queststatus.sql b/sql/updates/10828_characters_character_queststatus.sql new file mode 100644 index 00000000000..88681714a86 --- /dev/null +++ b/sql/updates/10828_characters_character_queststatus.sql @@ -0,0 +1,13 @@ +DELETE FROM character_queststatus WHERE `status` = 0; + +DROP TABLE IF EXISTS `character_queststatus_rewarded`; +CREATE TABLE `character_queststatus_rewarded` ( + `guid` int(10) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier', + `quest` int(10) unsigned NOT NULL default '0' COMMENT 'Quest Identifier', + PRIMARY KEY (`guid`,`quest`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Player System'; + +INSERT INTO character_queststatus_rewarded SELECT guid, quest FROM character_queststatus WHERE rewarded = 1; +DELETE FROM character_queststatus WHERE rewarded = 1; + +ALTER TABLE character_queststatus DROP COLUMN rewarded; -- cgit v1.2.3