Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

[MySQL] Saving and loading - What would you do?

Joined
May 17, 2007
Messages
2,474
Reaction score
681
Hello,

I'm currently working on an emulator, and just a while ago i started getting start on loading and saving.

I've been thinking, but i really can't decide, since I'm not sure which would be more efficient.

My first option:
When a user registers, only the one record is inserted (into 'characters' table). When the user tries to login, and after he/she has done so successfully, the server will try loading all information from the user (which is separate across multiple tables, and combines via mysql 'LEFT JOIN'), it'll run though all the information it has and apply them to the entity instance, if it runs into a NULL (which means the information isn't in the database yet) it'll automatically use a default value.
At saving, it'll insert or update, so that any defaults that have been generated at loading will be saved now.

My second option:
Simply insert all the required rows at registration (rows are inserted when from website when the registration is finished).



Downsides to first option: useless checks if the user has logged in once already, since all the tables will be generated after first login.
Upsides to first option: if any records from tables are deleted, it would insert default data instead of kicking player off saying it's character information is damaged/lost.


Downsides to second option: it could waste a bit of memory, since all tables are inserted at registration, and there could be spamming bots, and people who don't even manage to get online.
Upsides to first option: We don't have to check for anything in the server.


I also noted that the first option may screw up any search systems (via admincp, if we try looking a specific users).
 
Last edited:
Skilled Illusionist
Joined
Apr 6, 2007
Messages
384
Reaction score
2
If the account they make IS their in game info, then use the 2nd. Otherwise they'd actually make their character in the game right? You'd associate a character ID in a field for the account so if the id = -1 or something a character doesn't exist so no need to look for 10 rows that won't exist.
 
Joined
May 17, 2007
Messages
2,474
Reaction score
681
If the account they make IS their in game info, then use the 2nd. Otherwise they'd actually make their character in the game right? You'd associate a character ID in a field for the account so if the id = -1 or something a character doesn't exist so no need to look for 10 rows that won't exist.

Yes, the account is their character. Thanks.

I could probably just flag every account on registration has "unactivated" untill they login to the first time, and have a montly cron job cleaning up the database. (for the 2nd option)
 
Ginger by design.
Loyal Member
Joined
Feb 15, 2007
Messages
2,340
Reaction score
653
Yes, the account is their character. Thanks.

I could probably just flag every account on registration has "unactivated" untill they login to the first time, and have a montly cron job cleaning up the database. (for the 2nd option)

You can set default values in MySQL so you can create a new character which will automatically inherit all of the defaults (instead of setting them all to NULL). Further, if you have any kind of 'special' stuff you want to happen on first login, track create-date and last-login fields in their account data. The last-login will be NULL, this will let you do this, it'll also let you cache accounts if you wish since you can bring up all the accounts for players who've played in the past week when the server starts (or once every few minutes in some kind of caching daemon code).

Further, avoid ever querying MySQL in your code except in cases where it's a "only once in a while" event. Such as when a user logs on, load their data in from the tables (item data, skill data, friends, guild info, etc etc), and then throughout their play, do *NOT* insert crap and update fields in the database. You track that in a player object and when they log out then update everything (that includes item spawning, don't put items in a DB until someone logs off with a newly created item in their inventory, at which point you generate a unique ID for it, until then it just has an ephemeral offset ID [like some counter since uptime + 500 billion]). Then after they log off, keep their data stored in memory for a period of time after they log off. There's a percentage of users (and it's kinda large) who will log back in within 30 minutes of logging out. If you have their data ready to go, it can lower their data gather cycle from a few hundred ms to less than 1 ms, which when dealing with lots of concurrent connections is a nice load off. You could even track user habits and if this person has a high percentage of reconnecting, then you cache their data, otherwise you just discard it right away.

I can go on and on. I do have a really powerful engine that does most of this crap, though it's far from even alpha ready, hence why I'm not using it :).
 
Divine Celestial
Loyal Member
Joined
Sep 13, 2008
Messages
853
Reaction score
14
You could make a procedure that would receive user and pass, it would authenticate the user and if user and pass matches, it check last login time(like jMerliN said), if NULL, insert all data on the tables, then update last login(outside the if) and return. At the server you check if it returned true or false(for authentication) and then select any data you need.

Using a stored procedure would reduce the incoming and outgoing data from/to sql server, making that operation faster.

Use a default value for last login time(NULL), and also for the other data that may be defaulted.

Its late and Im not thinking straight, but if stored procedures wouldnt fit here, Im sure it would fit elsewhere, so keep it in mind :)
 
Joined
May 17, 2007
Messages
2,474
Reaction score
681
You can set default values in MySQL so you can create a new character which will automatically inherit all of the defaults (instead of setting them all to NULL). Further, if you have any kind of 'special' stuff you want to happen on first login, track create-date and last-login fields in their account data. The last-login will be NULL, this will let you do this, it'll also let you cache accounts if you wish since you can bring up all the accounts for players who've played in the past week when the server starts (or once every few minutes in some kind of caching daemon code).

Further, avoid ever querying MySQL in your code except in cases where it's a "only once in a while" event. Such as when a user logs on, load their data in from the tables (item data, skill data, friends, guild info, etc etc), and then throughout their play, do *NOT* insert crap and update fields in the database. You track that in a player object and when they log out then update everything (that includes item spawning, don't put items in a DB until someone logs off with a newly created item in their inventory, at which point you generate a unique ID for it, until then it just has an ephemeral offset ID [like some counter since uptime + 500 billion]). Then after they log off, keep their data stored in memory for a period of time after they log off. There's a percentage of users (and it's kinda large) who will log back in within 30 minutes of logging out. If you have their data ready to go, it can lower their data gather cycle from a few hundred ms to less than 1 ms, which when dealing with lots of concurrent connections is a nice load off. You could even track user habits and if this person has a high percentage of reconnecting, then you cache their data, otherwise you just discard it right away.

I can go on and on. I do have a really powerful engine that does most of this crap, though it's far from even alpha ready, hence why I'm not using it :).

Thanks for the suggestions on management mysql queries. Though, the server has a compliant website which has a script that depends on mysql having character's updated items atleast within 10 seconds, aswell as some other similar things.

Though, i could probably make a request for the items to be updated, etc. Have to think that out later :p
 
Back
Top