mysql serialization

[Red Squirrel]

My game server engine saves all data in serialized text files. I want to convert this to SQL but I will keep the serialize nature of it, but will make it so each item get's it's own row. This way I only have to save what changes, not the entire world (100's of thousands of items).

What would be the best type of SQL field to use for serialized data? I'm thinking text, and if the last bits do not make a full byte, I'll just add a "padding". But does SQL have a field type built in for storing binary data?

 

[Crusty]

BLOB

 

[JasonCoder]

BLOBs are the absolute slowest fields to retrieve and save. If it's something that is happening often I would consider the perf impact.

 

[Red Squirrel]

It will only be read once when the server app starts up, and it will be written to at given intervals only (I'm hoping to make it happen in the background).

This is not the best way of doing it, but it will be the fastest to do given the horrible state of this app and still be an improvement over the existing system.

Though that said would blob still be slower then text? or would they be equaly as slow?

 

[Crusty]

Yeah, BLOB's will get the job done but they aren't fast by any means. I better solution IMO is to store the serialized data on disk and use the database to store the location of the files.

 

[Red Squirrel]

Hmm that's not a bad idea actually... in fact come to think of it I could have a file that acts as an index and just stick to files, but reorganize how it's laid out and stuff. I'll look into that.

 

[blahblah99]

Define "serialized" data.. Alphanumeric?

If it's only alphanumeric, you can use a text field with a max limit on chars.

 

[Red Squirrel]

serialized like this:

WriteBool(1);
WriteBool(0);
WriteInt8(53);
WriteInt32(55253);

And so on. So I can end up with data that is not a multiple of 8 bits. Though if I go text I can always add "padding" at the end, that should not be a problem.

 

[blahblah99]

Well, mysql natively supports BOOLEAN data types, so if your data structure is fixed, you can always define the proper data types in the mysql table and let the internal engine handle the rest.

 

[Fallen Kell]

I was going to suggest the same thing. Create some basic tables which are literally have the fields of your game object. For instance say you are saving the information of a character. Have something like the following:

CREATE TABLE `p_data` (
'p_id' INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
'creation_date' DATETIME default NOW(),
'name' CHAR(16) NOT NULL,
'password' BLOB,
'strenght' INT(20) UNSIGNED default '0',
'agility' INT(20) UNSIGNED default '0',
'vitality' INT(20) UNSIGNED default '0',
'intellect' INT(20) UNSIGNED default '0',
'wisdom' INT(20) UNSIGNED default '0',
PRIMARY KEY(`a_id`),
)";

Basically you get the idea.... You can do this for other data, like items, weapons, environment, etc.