Database Optimisation - Delete Unused Accounts

[TouchOfFlames]

Pls for help - delete unused accounts by date last login.

 

MSSQL Querry

[mcbigmac]

use lin2db

select * from user_account

where last_login > 2013-08-29 replace date.

 

 

well if you want an automatic script you'll have to use joins\innerjoins to delete between lin2db\lin2world

Read up on SQL queries - and how delete from several tables with 1 select.

 

EDIT:

 

Ninja edit - i'd probably create multi'tiered Stored Procedure to run by SQL agent.

 

so you first delete from table where last_login > DATEADD(mm,-30,GETDATE()) --// example with 30 days not logged accounts.

from user_account

 

and then run a series of queries for user_data etc like this:

 

DELETE FROM [lin2db].[dbo].[user_auth] WHERE account Not In (SELECT account FROM [lin2db].[dbo].[user_account]);

DELETE FROM [lin2world].[dbo].[user_data] WHERE account_id Not In (SELECT uid FROM [lin2db].[dbo].[user_account]);

 

same for char_id in user_item and all other tables.

[TouchOfFlames]

use [lin2db]

select * from user_account

where last_login > 2013-17-01

 

returns same record all over the time , no matter what date do I setup.

Shows ALL records from user_account not sorted by date

 

where last_login = 2013-17-01

where last_login < 2013-17-01

last_login > DATEADD(mm,-30,GETDATE())

 

Does`t give Returns at all

 

use [lin2db]

DELETE * from user_account

where last_login > DATEADD(mm,-30,GETDATE())

 

Incorrect syntax near '*'.

[mcbigmac]

DELETE * from user_account

 

remove * not used - seriously try a bit yourself.

 

where last_login < 2013-17-01

 

needs to be > - it's reversed bits in the way dateformat is stored.

[TouchOfFlames]

Thx a lot.

[xeL]

Added option in my app if interested: http://www.maxcheaters.com/forums/index.php?/topic/168444-l2off-gm-panel/

[Sighed]

Btw and all the characters, if any, what happens with them ? It shouldn't be better to remove the characters and their data 1st and then lin2db ? In fact i didn't needed to optimize lin2db it isn't kicked so hard, lin2world has a nice kick on the harddrive.

[xeL]

Btw and all the characters, if any, what happens with them ? It shouldn't be better to remove the characters and their data 1st and then lin2db ? In fact i didn't needed to optimize lin2db it isn't kicked so hard, lin2world has a nice kick on the harddrive.

Good point. I think I will make a querry for that.

[mcbigmac]

Because associating the unique remove factor - by account id is easier than weird inner joins with login dates from user_auth.

 

Same with then having to remove data from user_item, and other bigger tables.

 

 

start from top, and remove down heirachy wise :P

[xeL]

Because associating the unique remove factor - by account id is easier than weird inner joins with login dates from user_auth.

 

Same with then having to remove data from user_item, and other bigger tables.

 

 

start from top, and remove down heirachy wise :P

I my next release of GM Panel I will add a future for that.

[Sighed]

Because associating the unique remove factor - by account id is easier than weird inner joins with login dates from user_auth.

 

Same with then having to remove data from user_item, and other bigger tables.

 

 

start from top, and remove down heirachy wise :P

And what about the content ? If new people creates new accounts the system won't assign them the empty numbers ? Giving new accounts other chars ? Or it adds numbers exponentially ?

Edited September 26, 2013 by Sighed

[xeL]

If you delete chars from acc and from all tables in lin2world base i doubt that this problem will happen.

Edited September 27, 2013 by xeL

[mcbigmac]

Primary Key solves those issues - with the account_id and name.

 

That's why starting to remove from the top - and then remove user_data , and then on to skill, item etc etc.