[SQL] Чистка базы characters - Форум

Страница 1 из 1 1
Модератор форума: Dimitro

[SQL] Чистка базы characters

Desed

Командир

Репутация: 1340

Некоторым серверам уже по 3-4 года, без вайпов.
Вот у меня бд весит 40 гигов, решил почистить.

Итак чистка работает по следующей выборке
SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0

name='' > удаленные персонажи. Не знаю как ну других серверах но они у меня удалются не полностью, чтобы их можно было восстановить.

logout_time<1370044800 > персонажи не заходившие с 1 июня 2013го года.

totaltime=0 = игровое время равно нулю, а точней создали персонажа и не разу на него не заходили.

Можете подставлять свои условия.

Код

TRUNCATE TABLE corpse;
TRUNCATE TABLE group_member;
TRUNCATE TABLE group_instance;
TRUNCATE TABLE groups;
DELETE FROM `item_instance` where owner_guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM `character_achievement` where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM `character_achievement_progress` where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM guild_bank_item where item_guid NOT IN (select guid from item_instance);
DELETE FROM character_arena_stats where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_aura where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_banned where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_battleground_data where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_declinedname where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_equipmentsets where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_gifts where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_glyphs where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_homebind where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_instance where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_inventory where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_lifes where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_pet where owner IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_pet_declinedname where owner IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_queststatus where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_queststatus_rewarded where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_queststatus_seasonal where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_queststatus_weekly where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_reputation where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_skills where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_social where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_spell where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_spell_cooldown  where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_talent where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM guild_member where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM item_refund_instance where player_guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM lag_reports where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM arena_team where captainGuid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
  DELETE FROM arena_team_member where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
  DELETE FROM gm_tickets where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
  DELETE FROM character_action where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
  DELETE FROM character_account_data where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
  DELETE FROM character_queststatus_daily where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
  DELETE FROM character_queststatus_monthly where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0;

Сообщение # 1 отредактировано Desed - Воскресенье, 16.03.2014, 16:51

OZOLIN

Центурион

Репутация: 194

Цитата Desed (

)

logout_time<1370044800 >

можешь объяснить как это высчитывается?

Сообщение # 2 написано 14.03.2014 в 09:12

infinium

Маршал

Репутация: 393

OZOLIN, Unix time.

SmartAI PvP|PvE Event (first job)

Сообщение # 3 отредактировано infinium - Пятница, 14.03.2014, 12:27

Desed

Командир

Репутация: 1340

http://www.onlineconversion.com/unix_time.htm

Балуйтесь)

Сообщение # 4 написано 14.03.2014 в 12:36

Hidenn

Скаут

Репутация: 0

Отдуши! +

Чтобы обрести знание, каждый день что-нибудь добавляй; чтобы обрести мудрость, каждый день от чего-нибудь избавляйся.

Сообщение # 5 написано 14.03.2014 в 12:40

Desed

Командир

Репутация: 1340

У себя в бд я удалил 219к чаров, потом в некоторых таблицах было удалено по 15-25 лям записей)
В общем эгегей бд

Добавлено (14.03.2014, 12:46)
---------------------------------------------

Код

[SQL]
DELETE FROM `item_instance` where owner_guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0);
Affected rows: 8052624
Time: 218.917ms

[SQL]
DELETE FROM `character_achievement` where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0);
Affected rows: 15444826
Time: 140.307ms

[SQL]
DELETE FROM character_reputation where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0);
Affected rows: 19731439
Time: 808.112ms

Урурур))

Сообщение # 6 написано 14.03.2014 в 12:46

infinium

Маршал

Репутация: 393

Для себя еще несколько запросов дописал:

Код

DELETE FROM arena_team where captainGuid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);   --  на случай если персонаж был удалён через базу
DELETE FROM arena_team_member where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM gm_tickets where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_action where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_account_data where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_queststatus_daily where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);
DELETE FROM character_queststatus_monthly where guid IN (SELECT guid FROM `characters` where  name='' or  logout_time<1370044800 or totaltime=0);

SmartAI PvP|PvE Event (first job)

Сообщение # 7 отредактировано infinium - Пятница, 14.03.2014, 13:26

Desed

Командир

Репутация: 1340

Цитата infinium (

)

Для себя еще несколько запросов дописал:

Спасиб, закреплю к первому посту

Сообщение # 8 написано 16.03.2014 в 16:50

[SQL] Чистка базы characters

Страница 1 из 1
1

Вход на портал