Оптимизация - Чистка базы данных - Форум портала - портал про WoW! Аддоны для WoW, читы для WoW, боты для WoW, сервера WoW, гайды и тактики для WoW, читы, 2.4.3, 3.3.5, катаклизм, Cataclysm.

Оптимизация - Чистка базы данных

rom4ins (Offline)

Дата: Среда, 10.02.2010, 12:10 | Сообщение # 1

Сотрудник WoW-SGP

---------

Пол:

Должность:

Ранг/Титул:

Репутация: 89 ±

Награды/Медали: 15 ±

Сервер: WoW-Europe

Если у Вас имя базы другое, то надо будет ascent сменить на имя Вашей базы, где хранится таблички.

Code

#Команда на удаление аккаунтов, которые не использовались или на которых нет персонажей.

DELETE FROM `ascent`.`accounts` where `acct` not in (select acct from `ascent`.`characters`);

Code

# Команда на удаление аккаунтов, на которые давно не заходили. lastlogin`)>*; где * - число дней с последнего захода.

DELETE FROM `ascent`.`accounts` WHERE DATEDIFF(NOW(), `lastlogin`)>30;

Code

# Удаление персонажей у которых нет аккаунтов. Удаление всех связей с удаленными персонажами.

DELETE FROM `ascent`.`characters` where `acct` not in (select acct from `ascent`.`accounts`);
DELETE FROM `ascent`.`corpses` where `guid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`guild_banklogs` where `player_guid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`guild_data` where `playerid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`guilds` where `leaderGuid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`instances` where `creator_guid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`mailbox` where `player_guid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`mailbox` where `sender_guid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`playercooldownitems` where `OwnerGuid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`playercooldownsecurity` where `OwnerGuid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`playeritems` where `ownerguid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`playerpets` where `ownerguid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`playerpetspells` where `ownerguid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`playerpetspells` where `ownerguid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`playersummonspells` where `ownerguid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`questlog` where `player_guid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`social` where `guid` not in (select guid from `ascent`.`characters`);
DELETE FROM `ascent`.`tutorials` where `playerId` not in (select guid from `ascent`.`characters`);