Member Discussions

I've been using MySQL for player storage for a while now and am curious if anybody else has done the same.

The problem I am currently running into is the ever growing variables in my Pc and Character (to lesser extent) classes that I wish to have saved for each player which is leading to a large (increasingly unruly) UPDATE clause.

I downloaded a few Mud sources that were claiming to be MySQL oriented to see how they solved the issue; but found majority of them to still be using flat files for a lot of saving routing. I started using MySQL for player files because when was encountering speed issues with Cygwin (tend to use my laptop when I do offline programming which runs Vista) and ofstream. The speed problems became noticeable after one of their updates a long while ago...

In the past when I was using functions to convert bits saved to a human readable and easily modifiable form. Once I started using MySQL I stopped that and started saving them as numbers only. Lately I've considering going to back to that system, but its going to just compound my issues it seems.

Has anybody else ever run into this issue? Should I just deal with the unruly UPDATE clause or possibly go back to flat files solely for my player files? Any thoughts and input are appreciated...

Hi,

Recently converted my CircleMUD based game to use MySQL for pretty much everything. It's going to be absolutely everything when I'm done.

If a player save table is getting too wide, you can always break it up into smaller ones. The guiding principle should be info that is likely to be accessed/modified together belongs together.

Even with that rule in mind, some player tables can get wide if you're adding a lot of properties, and the queries can get long. In that case, just be zen about it and remember how much you love your database.

As for human editable flags, there's a trick that can get you the best of both worlds. See my post here:

http://www.mudbytes.net/index.php?a=topic&t=3729&p=60032#p60032

Hope this helps.

(Comment added by plamzi on Mon Jan 16 9:53:04 2012)

P. S. You can use views to pull data from multiple player tables without having to repeatedly write joins in your code. I've also found it useful to write any maintenance routines as db procedures, which you can tweak as needed without disrupting the game server.

Thanks for the VIEW suggestion its something I wasn't aware of, I will read up on that later tonight once I get home.

Right now I'm at ~80 rows in my player table and begun to feel like its beginning to take on a life of its own. I wouldn't be surprised if my UPDATE clause tried to walk away at this point.

I did scan through my Pc/Character classes and did see some places I could take better advantage of the parsing functions I wrote a while ago for arrays in the MySQL tables. Can cut some of the table bloat there.

Right now I'm at ~80 rows in my player table and begun to feel like its
beginning to take on a life of its own. I wouldn't be surprised if my UPDATE
clause tried to walk away at this point.

Do you actually mean 80 rows (as in, 80 players in your database), or 80
columns (i.e. fields) in the table? If the latter, then that's a pretty wide table,
though MySQL can handle much, much more. As another poster suggested, a
good way to go is to break your table up into logically grouped sets of fields. So
you might have your core data (name, sex, etc.) in the main table and then one
or more peripheral tables. Then you can just grab what you need to grab.

That said, there's nothing wrong with a large UPDATE statement. MySQL can
handle a lot, and, unless you're doing thousands every minute, you shouldn't
really run into any issues.

I did scan through my Pc/Character classes and did see some places I
could take better advantage of the parsing functions I wrote a while ago for
arrays in the MySQL tables. Can cut some of the table bloat there.

If you mean de-normalizing data so that one MySQL column contains multiple
player fields, that's certainly an option, but be careful about too much de-
normalization as it makes searching and other related tasks in MySQL a lot
harder. Generally, though, if you won't be using the data in WHERE or ORDER BY
clauses, and you don't need to SELECT it separately, de-normalization is a safe
route that can yield pretty good speed and memory usage improvements.

I'm actually in the process of converting my project to use MySQL exclusively for
all storage as well, so it's good for me to talk this through!

My player-files are currently at 98 columns. It doesn't bother me, because I use
MYSQL++ ( http://tangentsoft.net/mysql++/ ) in my code to access just the
columns I need. I'm not certain if that is your problem with unruly UPDATE
clause, but it helped me out. The final UPDATE query is a beast, but I create the
string a piece at a time as needed, then send the fully assembled query. Being
able to refer to a column by name, is an enormous help.

My pfiles, objects, mobs, skills, and many other tables are held in MySQL, and I
found it to be a much easier way to maintain information. We can use standard
php web-forms for staff to make changes to races, skills and other values,
without forcing a re-compile of the code. We are also using the MySQL to
evaluate building, and enforce quality standards, all without touching the game
code.

If your mud compiles as C++, the C++ ODB mapping system (
http://www.codesynthesis.com/products/odb/ ) is also a good way to go.
Using an ORM wrapper like ODB allows you to avoid writing your own queries
altogether, which is far more fool-proof and allows for automatic injection
prevention and such.