Life is Feudal Official Forum

[Eslake]

This one was already cleaned up since I was using it to teach someone some of the basic concepts.

The game stores every food every character ever eats. While this actually doesn't add up to much, even on a high population server, every little bit helps.
The first run of this reduced our food_eaten table from over 1700 entries, to 242.

The game only needs to know the most recent 4 foods eaten, but to be safe, we keep 5.

USAGE
Run the script to create the procedure
proc_Reduce_food_eaten

Then, during maintenance, and AFTER BACKUP,
CALL proc_Reduce_food_eaten;

BACK UP the database before making any changes, specially before running scripts that modify data.
DO NOT call this procedure while the server is active. It is a maintenance script.

CREATE PROCEDURE `proc_Reduce_food_eaten`()
BEGIN
DECLARE CharacterID INT;
SET CharacterID=0;

CREATE TABLE IF NOT EXISTS `new_foods` LIKE `food_eaten`;
TRUNCATE TABLE `new_foods`; -- in case it was left over somehow from a previous attempt.

chars_Loop: Loop

-- Each character gets the same process

SELECT `ID` INTO CharacterID FROM `character` WHERE `ID`>CharacterID ORDER BY `ID` LIMIT 1;

INSERT INTO `new_foods` (`CharID`, `Time`, `FoodTypeID`, `Complexity`) SELECT `CharID`, `Time`, `FoodTypeID`, `Complexity` FROM food_eaten WHERE CharID=CharacterID ORDER BY `ID` DESC LIMIT 5;

IF (SELECT COUNT(*) FROM `Character` WHERE `ID`>CharacterID)> 0 THEN

ITERATE chars_Loop;

ELSE

LEAVE chars_Loop;

END IF;

END LOOP chars_Loop;
-- Replacing the old food_eaten table with the new one
DROP TABLE `food_eaten`;
RENAME TABLE `new_foods` TO `food_eaten`;
END

-- As a copy/paste code snippet

Code: Select all

CREATE PROCEDURE `proc_Reduce_food_eaten`()
BEGIN
DECLARE CharacterID INT;SET CharacterID=0;
CREATE TABLE IF NOT EXISTS `new_foods` LIKE `food_eaten`;
TRUNCATE TABLE `new_foods`;-- in case it was left over somehow from a previous attempt.

chars_Loop: Loop
   -- Each character gets the same process.
   SELECT `ID` INTO CharacterID FROM `character` WHERE `ID`>CharacterID ORDER BY `ID` LIMIT 1;
   INSERT INTO `new_foods` (`CharID`, `Time`, `FoodTypeID`, `Complexity`)
    SELECT `CharID`, `Time`, `FoodTypeID`, `Complexity` FROM food_eaten WHERE CharID=CharacterID ORDER BY `ID` DESC LIMIT 5;
   IF (SELECT COUNT(*) FROM `Character` WHERE `ID`>CharacterID)> 0 THEN
      ITERATE chars_Loop;
   ELSE
      LEAVE chars_Loop;
   END IF;
END LOOP chars_Loop;
-- Replacing the old food_eaten table with the new one.
DROP TABLE `food_eaten`;
RENAME TABLE `new_foods` TO `food_eaten`;
END

[Gms0012]

good input..

thx

[Razoreqx]

much thanks!! Works great