Clean database with a command?

[TKO]

Hey i wanna know how i can remove alot of of data that isnt in use on the database?
i got one for players: DELETE FROM players WHERE level < 1000 AND lastlogin < UNIX_TIMESTAMP() - 30*24*60*60
Some for account? some for somting else that i can clean and remove some stuff that can make the db smaller?

 

[ddamiann]

I don't know, how to make a SQL query, but I made something other. Put this PHP script on your server, run once and delete:

$conn = mysql_connect('localhost', 'root', 'password');
mysql_select_db('ots', $conn);
$accounts = mysql_query('SELECT * FROM accounts WHERE id > 1')
while($account = mysql_fetch_assoc($accounts)) {
	echo 'Checking account with id '.$account['id'].'...<br/><br/>';
	$i = 0;
	$players = mysql_query('SELECT * FROM players WHERE account_id='.$account['id']);
	while($player = mysql_fetch_assoc($players)) {
		if($player['level'] < 1000 and $player['lastlogin'] < time()-30*24*60*60) {
			echo 'Player '.$player['name'].' has been deleted.<br/>';
			mysql_query('DELETE FROM players WHERE id='.$player['id']);
		}
		else {
			$i++;
		}
	}
	if($i == 0) {
		mysql_query('DELETE FROM accounts WHERE id='.$account['id']);
		echo 'Account with id '.$account['id'].' has been deleted.<br/>';
	}
	echo '<hr/>';
}

You have just to fill database connection parameters in first two lines.

It will delete all accounts on that are ONLY players with level lower than 1000 and that didn't login for 30 days. If on account is any active character, it won't be delete. Empty accounts will be deleted too. Should work fine, but better make a backup.

 

[TKO]

i did mostly want sql commands... not a php script...

 

[cbrm]

this one used to delete my empty accounts with no players:

DELETE * FROM `accounts` WHERE NOT EXISTS (SELECT `id` FROM `players` WHERE `accounts`.`id` = `players`.`account_id`);

 

[TKO]

Thanks cyberM is there any else that is good for removing inacitiv things?
CyberM that dint work:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM `accounts` WHERE NOT EXISTS (SELECT `id` FROM `players` WHERE `accounts`.' at line 1

 

[Cykotitan]

Try

DELETE FROM `accounts` WHERE NOT EXISTS (SELECT `id` FROM `players` WHERE `accounts`.`id` = `players`.`account_id` LIMIT 1);

Create a backup of accounts in case something fails !

 

[TKO]

thanks i will try this