Trimming the database down

[ElDubya]

Hi all, 

 

So I noticed this today : 

 

 

And was just wondering if 28,578 items in the database is a lot, and if so, where should I start trimming?

 

In the player_login for example, I have a HEAP of characterID listed as 0. Can I trim those? What are they exactly? Anything else I should do to trim the overall size, if that size (20,xxx) is an issue?

 

Thanks in advance.

[ddmankiller]

It records when the player logs in, I delete that table every month or so.

[Sandbird]

I use this to clear dead characters (once in a week)

DELETE FROM `character_data`
USING character_data, character_data AS tmpcharacter_data
WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID
AND character_data.CharacterID<tmpcharacter_data.CharacterID
AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)

It basically leaves the players that havent spawned back...so to be safe it will copy their stats to the new character when they make one.....so even if they are dead....and no alive character...that will be spared from the deletion.

 

Then i run this once a day:

DELETE FROM `character_data` WHERE `Worldspace` LIKE '[]' AND `Medical` LIKE '[]'

Left over from 1.0.4.2 where some times players would bug out and spawn as seaguls or something....just to delete garbage

[jahangir13]

Hi Sandbird,

 

I do the second so...it also deletes players where the character is deleted. These [] for Inventory and Medical tells me that players have not been able to connect at all (wrong mods or something):

DELETE FROM Character_DATA, Player_DATA USING Character_DATA, Player_DATA
               WHERE Character_DATA.LastLogin < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 DAY)
               AND Worldspace = '[]'
               AND Medical = '[]'
               AND Character_DATA.PlayerUID = Player_DATA.PlayerUID;

 

 

For the first I have an Alive within the query:

DELETE from Character_DATA
               USING Character_DATA, Character_DATA as tempchartable
               WHERE (Character_DATA.PlayerUID = tempchartable.PlayerUID)
               AND (NOT Character_DATA.alive = tempchartable.alive)
               AND (Character_DATA.alive = 0)";

 

So only if there is a Alive = 1 line for this character it will delete all the Alive = 0 lines/rows. So if for whatever reason the player just died and did not login again when the query is executed (and the is no new line for the player with Alive = 1).

So humanity is kept for this player.

 

Ah, what I forgot: I have a seperate table 'All_time_players' which keep the players who logged in whenever. It's a trigger on the normal player_data table which just inserts a new row in the all_player_table whenener a new one is inserted in player_data table. It's just that I know how many players and who have been there and to keep the player_data table small as we need to load in any row via file system in the linux server version.

[ElDubya]

I use this to clear dead characters (once in a week)

DELETE FROM `character_data`
USING character_data, character_data AS tmpcharacter_data
WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID
AND character_data.CharacterID<tmpcharacter_data.CharacterID
AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)

It basically leaves the players that havent spawned back...so to be safe it will copy their stats to the new character when they make one.....so even if they are dead....and no alive character...that will be spared from the deletion.

 

Then i run this once a day:

DELETE FROM `character_data` WHERE `Worldspace` LIKE '[]' AND `Medical` LIKE '[]'

Left over from 1.0.4.2 where some times players would bug out and spawn as seaguls or something....just to delete garbage

 

Would I need a tmpcharacter_data field in the DB for the top one to run correctly?

[jahangir13]

"character_data AS tmpcharacter_data" means: take the character_DATA table and make a so to say copy to 'shadow' or virtual table tmpcharacter_data (in memory I guess). That's then internally used in the query for comparison.

So nothing to do on your side. Just make sure you use the correct table name notation (Character_DATA / character_data / ...).

 

But before you use something you should at least understand a bit what it does ,))

 

DELETE FROM `character_data` (delete rows from table character_data)
USING character_data, character_data AS tmpcharacter_data (use 2 tables for value comparison: character_data and a copy of it with name tmpcharacter_data)
WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID (character ID of the row in character_data shall be unequal to the  characterID in tmpcharacter_data table)
AND character_data.CharacterID<tmpcharacter_data.CharacterID (character ID of the row in charater_data should be smaller than the ones in tmpchar-table if there are any)
AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID) (Player ID shall be the same of both rows in char_data and tmpchar_data table)

 

So line 1 in character_data table is the first row in question: delete row or not

In the where clause all 3 lines need to be true that data to delete is returned.

So if in our copied table tmpchar_data are rows available which are from the same player (same PlayerUID as in our row in question) AND these row(s) have a higher Char ID than the one in question AND the Char ID is NOT the same (as that would be bad as we may delete the only row).

...then delete this row in character_data.

 

Take second row in character_data and do the same checks and so on.

 

So in the end you always have the newest character row of each player left. There can still be characters with Alive=0...if there was no row available already with Alive=1 (player died and logged out or something).

If there is one or more with Alive=0 and 1 with Alive=1 (the new one) available, then we delete all the ones with Alive=0.

[ElDubya]

Thanks jahangir13, I was slowly wrapping my head around this when Sandbird posted it but your explanation is fantastic. :)