Banco de dados

[crazyshock]

galera meu server ta grande e o serve começo a da lag tenho certeza que seje me banco de dados que esta grande , so que como posso limpar?
queria sbe como posso fazer pra tirar contas que nao loga a mais de 2 meses..?
e limpar os chars que nao loga a 2 meses , e os itens por que pesa tbm..
alguem pode ajuda?

vlwu

[christiand]

na query do mysql vc roda isso, é para deletar char q não loga a mais de 60 dias.

-- more than ... days
SET @dt = 60;

DELETE FROM accounts WHERE DATEDIFF( CURRENT_DATE( ) , FROM_UNIXTIME( `lastactive` /1000 ) ) > @dt;

DELETE FROM accounts WHERE login NOT IN (SELECT account_name FROM characters);
DELETE FROM account_data WHERE account_name NOT IN (SELECT login FROM accounts);
DELETE FROM characters WHERE account_name NOT IN (SELECT login FROM accounts);
DELETE FROM character_friends WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_hennas WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_macroses WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_quests WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_recipebook WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_shortcuts WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_skills WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_skills_save WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM clan_data WHERE leader_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM clan_privs WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_skills WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM pets WHERE item_obj_id NOT IN (SELECT object_id FROM items WHERE owner_id IN (SELECT obj_Id FROM characters));
DELETE FROM items WHERE owner_id NOT IN (SELECT obj_Id FROM characters) AND owner_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM seven_signs WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM augmentations WHERE item_id NOT IN (SELECT item_id FROM items);
UPDATE characters SET clanid=0,title="",clan_privs=0 where clanid NOT IN (SELECT clan_id FROM clan_data);
UPDATE clanhall SET ownerID=0,paidUntil=0 where ownerID NOT IN (SELECT clan_id FROM clan_data);

lembrando q eu aconcelho fazer um backup antes de rodar isso ai.


ate mais...

[Epica]

opa!

Esta eu não sabia ^^ gostei xP.

[crazyshock]

Ai limpa tudo ne , conta , char ate os Itens tbm ?

[Setokaiba]

Correto... Tudo de um character que não loga por determinado tempo.
E lembrando que isso não é só para Kamael, serve para todas as versões de L2.

[crazyshock]

Blz vo fazer um backupp dai vo fazer isso ai .. so executar a sql e ja eras.. "!!

---

mais tem um negocio na minha tabela é CharId , ai tem obj_id , e char_id vo ter que muda ne?

[Setokaiba]

Positivo xD
Basta alterar o comando da query e ser feliz!