• Страница 1 из 1
  • 1
Модератор форума: Dimitro  
[SQL] Чистка базы characters
Desed
Командир
Некоторым серверам уже по 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
Центурион
Цитата Desed ()
logout_time<1370044800 >

можешь объяснить как это высчитывается?
Сообщение # 2 написано 14.03.2014 в 09:12
infinium
Маршал
Сообщение # 3 отредактировано infinium - Пятница, 14.03.2014, 12:27
Desed
Командир
Сообщение # 4 написано 14.03.2014 в 12:36
Hidenn
Скаут
Отдуши! +
Чтобы обрести знание, каждый день что-нибудь добавляй; чтобы обрести мудрость, каждый день от чего-нибудь избавляйся.
Сообщение # 5 написано 14.03.2014 в 12:40
Desed
Командир
У себя в бд я удалил 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
Маршал
Для себя еще несколько запросов дописал:
Код
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);
Сообщение # 7 отредактировано infinium - Пятница, 14.03.2014, 13:26
Desed
Командир
Цитата infinium ()
Для себя еще несколько запросов дописал:

Спасиб, закреплю к первому посту
Сообщение # 8 написано 16.03.2014 в 16:50
  • Страница 1 из 1
  • 1
Поиск: