· 8 years ago · May 03, 2018, 09:20 AM
1commit f33935d5fb1fb2cd88574d6de4221b44fbe1b919
2Author: AlexDereka <dereka.alex@gmail.com>
3Date: Sun Mar 21 14:08:45 2010 +0300
4
5 [9611] Add index for some tables in characters DB.
6
7diff --git a/sql/characters.sql b/sql/characters.sql
8index 2fcab08..b01b2cc 100644
9--- a/sql/characters.sql
10+++ b/sql/characters.sql
11@@ -21,7 +21,7 @@
12
13 DROP TABLE IF EXISTS `character_db_version`;
14 CREATE TABLE `character_db_version` (
15- `required_9375_01_characters_character_glyphs` bit(1) default NULL
16+ `required_9611_01_characters` bit(1) default NULL
17 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Last applied sql update to DB';
18
19 --
20@@ -466,7 +466,8 @@ CREATE TABLE `character_equipmentsets` (
21 `item17` int(11) NOT NULL default '0',
22 `item18` int(11) NOT NULL default '0',
23 PRIMARY KEY (`setguid`),
24- UNIQUE KEY `idx_set` (`guid`,`setguid`,`setindex`)
25+ UNIQUE KEY `idx_set` (`guid`,`setguid`,`setindex`),
26+ INDEX `Idx_setindex` (`setindex`)
27 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
28
29 --
30@@ -898,7 +899,9 @@ CREATE TABLE `corpse` (
31 `instance` int(11) unsigned NOT NULL default '0',
32 PRIMARY KEY (`guid`),
33 KEY `idx_type` (`corpse_type`),
34- KEY `instance` (`instance`)
35+ KEY `instance` (`instance`),
36+ INDEX `Idx_player`(`player`),
37+ INDEX `Idx_time`(`time`)
38 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Death System';
39
40 --
41@@ -978,7 +981,8 @@ CREATE TABLE `group_member` (
42 `memberGuid` int(11) unsigned NOT NULL,
43 `assistant` tinyint(1) unsigned NOT NULL,
44 `subgroup` smallint(6) unsigned NOT NULL,
45- PRIMARY KEY (`groupId`,`memberGuid`)
46+ PRIMARY KEY (`groupId`,`memberGuid`),
47+ INDEX `Idx_memberGuid`(`memberGuid`)
48 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Groups';
49
50 --
51@@ -1036,7 +1040,9 @@ CREATE TABLE `guild_bank_eventlog` (
52 `DestTabId` tinyint(1) unsigned NOT NULL default '0' COMMENT 'Destination Tab Id',
53 `TimeStamp` bigint(20) unsigned NOT NULL default '0' COMMENT 'Event UNIX time',
54 PRIMARY KEY (`guildid`,`LogGuid`,`TabId`),
55- KEY `guildid_key` (`guildid`)
56+ KEY `guildid_key` (`guildid`),
57+ INDEX `Idx_PlayerGuid`(`PlayerGuid`),
58+ INDEX `Idx_LogGuid`(`LogGuid`)
59 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
60
61 --
62@@ -1060,7 +1066,8 @@ CREATE TABLE `guild_bank_item` (
63 `item_guid` int(11) unsigned NOT NULL default '0',
64 `item_entry` int(11) unsigned NOT NULL default '0',
65 PRIMARY KEY (`guildid`,`tabid`,`slotid`),
66- KEY `guildid_key` (`guildid`)
67+ KEY `guildid_key` (`guildid`),
68+ INDEX `Idx_item_guid`(`item_guid`)
69 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
70
71 --
72@@ -1133,7 +1140,10 @@ CREATE TABLE `guild_eventlog` (
73 `PlayerGuid2` int(11) NOT NULL COMMENT 'Player 2',
74 `NewRank` tinyint(2) NOT NULL COMMENT 'New rank(in case promotion/demotion)',
75 `TimeStamp` bigint(20) NOT NULL COMMENT 'Event UNIX time',
76- PRIMARY KEY (`guildid`, `LogGuid`)
77+ PRIMARY KEY (`guildid`, `LogGuid`),
78+ INDEX `Idx_PlayerGuid1`(`PlayerGuid1`),
79+ INDEX `Idx_PlayerGuid2`(`PlayerGuid2`),
80+ INDEX `Idx_LogGuid`(`LogGuid`)
81 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'Guild Eventlog';
82
83 --
84@@ -1195,7 +1205,8 @@ CREATE TABLE `guild_rank` (
85 `rname` varchar(255) NOT NULL default '',
86 `rights` int(3) unsigned NOT NULL default '0',
87 `BankMoneyPerDay` int(11) unsigned NOT NULL default '0',
88- PRIMARY KEY (`guildid`,`rid`)
89+ PRIMARY KEY (`guildid`,`rid`),
90+ INDEX `Idx_rid`(`rid`)
91 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Guild System';
92
93 --
94@@ -1453,7 +1464,9 @@ CREATE TABLE `petition_sign` (
95 `playerguid` int(11) unsigned NOT NULL default '0',
96 `player_account` int(11) unsigned NOT NULL default '0',
97 `type` int(10) unsigned NOT NULL default '0',
98- PRIMARY KEY (`petitionguid`,`playerguid`)
99+ PRIMARY KEY (`petitionguid`,`playerguid`,
100+ INDEX `Idx_playerguid`(`playerguid`),
101+ INDEX `Idx_ownerguid`(`ownerguid`)
102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Guild System';
103
104 --
105diff --git a/sql/updates/9611_01_characters.sql b/sql/updates/9611_01_characters.sql
106new file mode 100644
107index 0000000..94e97c3
108--- /dev/null
109+++ b/sql/updates/9611_01_characters.sql
110@@ -0,0 +1,15 @@
111+ALTER TABLE character_db_version CHANGE COLUMN required_9375_01_characters_character_glyphs required_9611_01_characters bit;
112+
113+ALTER TABLE `group_member` ADD INDEX `Idx_memberGuid`(`memberGuid`);
114+ALTER TABLE `guild_eventlog` ADD INDEX `Idx_PlayerGuid1`(`PlayerGuid1`);
115+ALTER TABLE `guild_eventlog` ADD INDEX `Idx_PlayerGuid2`(`PlayerGuid2`);
116+ALTER TABLE `guild_bank_eventlog` ADD INDEX `Idx_PlayerGuid`(`PlayerGuid`);
117+ALTER TABLE `petition_sign` ADD INDEX `Idx_playerguid`(`playerguid`);
118+ALTER TABLE `petition_sign` ADD INDEX `Idx_ownerguid`(`ownerguid`);
119+ALTER TABLE `guild_eventlog` ADD INDEX `Idx_LogGuid`(`LogGuid`);
120+ALTER TABLE `guild_bank_eventlog` ADD INDEX `Idx_LogGuid`(`LogGuid`);
121+ALTER TABLE `guild_bank_item` ADD INDEX `Idx_item_guid`(`item_guid`);
122+ALTER TABLE `corpse` ADD INDEX `Idx_player`(`player`);
123+ALTER TABLE `corpse` ADD INDEX `Idx_time`(`time`);
124+ALTER TABLE `guild_rank` ADD INDEX `Idx_rid`(`rid`);
125+ALTER TABLE `character_equipmentsets` ADD INDEX `Idx_setindex` (`setindex`);
126\ No newline at end of file
127diff --git a/sql/updates/Makefile.am b/sql/updates/Makefile.am
128index 09387f8..792c2e2 100644
129--- a/sql/updates/Makefile.am
130+++ b/sql/updates/Makefile.am
131@@ -89,6 +89,7 @@ pkgdata_DATA = \
132 9539_01_mangos_spell_bonus_data.sql \
133 9589_01_mangos_creature_template.sql \
134 9590_01_mangos_db_script_string.sql \
135+ 9611_01_characters.sql \
136 README
137
138 ## Additional files to include when running 'make dist'
139@@ -158,4 +159,5 @@ EXTRA_DIST = \
140 9539_01_mangos_spell_bonus_data.sql \
141 9589_01_mangos_creature_template.sql \
142 9590_01_mangos_db_script_string.sql \
143+ 9611_01_characters.sql \
144 README
145diff --git a/src/game/GlobalEvents.cpp b/src/game/GlobalEvents.cpp
146index 7226364..837a5f9 100644
147--- a/src/game/GlobalEvents.cpp
148+++ b/src/game/GlobalEvents.cpp
149@@ -75,7 +75,7 @@ static void CorpsesErase(bool bones,uint32 delay)
150 {
151 ///- Get the list of eligible corpses/bones to be removed
152 //No SQL injection (uint32 and enum)
153- CharacterDatabase.AsyncPQuery(&CorpsesEraseCallBack, bones, "SELECT guid,position_x,position_y,map,player FROM corpse WHERE UNIX_TIMESTAMP()-time > '%u' AND corpse_type %s '0'", delay, (bones ? "=" : "<>"));
154+ CharacterDatabase.AsyncPQuery(&CorpsesEraseCallBack, bones, "SELECT guid,position_x,position_y,map,player FROM corpse WHERE time > (UNIX_TIMESTAMP()+'%u') AND corpse_type %s '0'", delay, (bones ? "=" : "<>"));
155 }
156
157 /// not thread guarded variant for call from other thread
158diff --git a/src/game/Player.cpp b/src/game/Player.cpp
159index e2bb2a0..aa65246 100644
160--- a/src/game/Player.cpp
161+++ b/src/game/Player.cpp
162@@ -4114,8 +4114,7 @@ void Player::DeleteFromDB(uint64 playerguid, uint32 accountId, bool updateRealmC
163 CharacterDatabase.PExecute("DELETE FROM character_achievement WHERE guid = '%u'",guid);
164 CharacterDatabase.PExecute("DELETE FROM character_achievement_progress WHERE guid = '%u'",guid);
165 CharacterDatabase.PExecute("DELETE FROM character_equipmentsets WHERE guid = '%u'",guid);
166- CharacterDatabase.PExecute("DELETE FROM guild_eventlog WHERE PlayerGuid1 = '%u'",guid);
167- CharacterDatabase.PExecute("DELETE FROM guild_eventlog WHERE PlayerGuid2 = '%u'",guid);
168+ CharacterDatabase.PExecute("DELETE FROM guild_eventlog WHERE PlayerGuid1 = '%u' OR PlayerGuid2 = '%u'",guid, guid);
169 CharacterDatabase.PExecute("DELETE FROM guild_bank_eventlog WHERE PlayerGuid = '%u'",guid);
170 CharacterDatabase.CommitTransaction();
171
172diff --git a/src/shared/revision_nr.h b/src/shared/revision_nr.h
173index 2d214af..d94bfab 100644
174--- a/src/shared/revision_nr.h
175+++ b/src/shared/revision_nr.h
176@@ -1,4 +1,4 @@
177 #ifndef __REVISION_NR_H__
178 #define __REVISION_NR_H__
179- #define REVISION_NR "9610"
180+ #define REVISION_NR "9611"
181 #endif // __REVISION_NR_H__
182diff --git a/src/shared/revision_sql.h b/src/shared/revision_sql.h
183index 061165e..d76a2e8 100644
184--- a/src/shared/revision_sql.h
185+++ b/src/shared/revision_sql.h
186@@ -1,6 +1,6 @@
187 #ifndef __REVISION_SQL_H__
188 #define __REVISION_SQL_H__
189- #define REVISION_DB_CHARACTERS "required_9375_01_characters_character_glyphs"
190+ #define REVISION_DB_CHARACTERS "required_9611_01_characters"
191 #define REVISION_DB_MANGOS "required_9590_01_mangos_db_script_string"
192 #define REVISION_DB_REALMD "required_9010_01_realmd_realmlist"
193 #endif // __REVISION_SQL_H__