[Guide] How to clean up your DB

[Ranik]

Hey guys!

Im sure you've all been at the point when you delete players and guilds out of your database to cut down on the loading time of your realm. Here is a simple sql script to make sure you remove all the pesky items that can bog down your database : Rename `character-database` and `world-database` to fit your custom server.

For Guilds :

DELETE FROM `character-database`.`guild_bankitems` WHERE `guildId` NOT IN (SELECT `guildId` FROM `character-database`.`guilds`);
DELETE FROM `character-database`.`guild_banklogs` WHERE `guildid` NOT IN (SELECT `guildId` FROM `character-database`.`guilds`);
DELETE FROM `character-database`.`guild_banktabs` WHERE `guildId` NOT IN (SELECT `guildId` FROM `character-database`.`guilds`);
DELETE FROM `character-database`.`guild_data` WHERE `guildid` NOT IN (SELECT `guildId` FROM `character-database`.`guilds`);
DELETE FROM `character-database`.`guild_logs` WHERE `guildid` NOT IN (SELECT `guildId` FROM `character-database`.`guilds`);
DELETE FROM `character-database`.`guild_ranks` WHERE `guildId` NOT IN (SELECT `guildId` FROM `character-database`.`guilds`);

For Players :

DELETE FROM `characters-database`.`characters` WHERE `acct` NOT IN (SELECT `acct` FROM `main-accounts`.`accounts`);
DELETE FROM `characters-database`.`playeritems` WHERE `ownerguid` NOT IN (SELECT `guid` FROM `characters-database`.`characters`);
DELETE FROM `characters-database`.`tutorials` WHERE `playerId` NOT IN (SELECT `guid` FROM `characters-database`.`characters`);
DELETE FROM `characters-database`.`social_friends` WHERE `character_guid` NOT IN (SELECT `guid` FROM `characters-database`.`characters`);
DELETE FROM `characters-database`.`social_ignores` WHERE `character_guid` NOT IN (SELECT `guid` FROM `characters-database`.`characters`);
DELETE FROM `characters-database`.`questlog` WHERE `player_guid` NOT IN (SELECT `guid` FROM `characters-database`.`characters`);
DELETE FROM `characters-database`.`corpses` WHERE `guid` NOT IN (SELECT `guid` FROM `characters-database`.`characters`);
DELETE FROM `characters-database`.`mailbox` WHERE `player_guid` NOT IN (SELECT `guid` FROM `characters-database`.`characters`);

For Items :

DELETE FROM `character-database`.`playeritems` WHERE `entry` NOT IN (SELECT `entry` FROM `world-database`.`items`);

For Creatures :

DELETE FROM `world-database`.`creature_names` WHERE `entry` NOT IN (SELECT `entry` FROM `world-database`.`creature_proto`);

That should help ya keep your databases running at peak performance!

[kingviper]

ive seen this a couple times already
still good to know though

[Glorianglorre]

Nice guied