Page 1 of 1
MySQL efficiency questions
Posted: Mon Jan 04, 2010 8:23 pm
by OldRod
Ok, before I get too far into this game, I'm having doubts about how my player database tables are set up.
Currently I have a users table:
Code: Select all
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` varchar(32) NOT NULL,
... other stuff clipped...
Then I have a charinfo table:
Code: Select all
`userid` int(11) NOT NULL,
`charid` smallint(2) NOT NULL,
`currlocation` int(11) NOT NULL,
`strength` smallint(3) NOT NULL,
`dexterity` smallint(3) NOT NULL,
`iq` smallint(3) NOT NULL,
`toughness` smallint(3) NOT NULL,
`charisma` smallint(3) NOT NULL,
`attack` int(5) NOT NULL,
`defense` int(5) NOT NULL,
`health` int(5) NOT NULL,
`fatigue` smallint(3) NOT NULL
The userid in charinfo is the same id in users, to link the two together. These are stats that are present at character creation time, or derived from other permanent stats, so every player will have these.
I'm starting to add more skills to the game - some which all players have, and some which you have to train before you get (so not everyone will have them). Do I keep adding fields to charinfo to hold these? Or do I make a separate table for skills that not everyone will have and do something like this instead?:
Code: Select all
`userid` int(11) NOT NULL,
`skillname` varchar(30) NOT NULL,
`skillnum` smallint(3) NOT NULL
Would I be better off combining 'users' and 'charinfo' and using simply use the third table for skills/stats that not everyone has? Or should I put all skills/stats in one table, no matter whether every character has them or not? Or does it really matter?
Re: MySQL efficiency questions
Posted: Mon Jan 04, 2010 9:34 pm
by hallsofvallhalla
one thing I recommend is reading up about indexing. It will make your sql queries much faster. I do not go into detail on it in my videos because it really separates the men from the boys. With beginners it is not so important but as you grow it should be looked into.
Second, I originally had a separate table for skills called skills, I also had a pid(player ID) that was attached to the player. I changed to placing all my skills in my players table. This way when you load every page you already have all the skills in the player array and you don't have to make a second query. Less queries the better. I also took a friends advice(Falken) and got away from ids. This number builds and builds until it becomes more weight than worth.
For a small game its fine but a larger game with a few hundred concurrent players will quickly become several thousand created accounts and taking up more memory. Not only int he db but also as queries. I instead use just the name.
Re: MySQL efficiency questions
Posted: Mon Jan 04, 2010 11:10 pm
by OldRod
If I put all the skills in the main character file, then lots of characters will have lots of zeroes for skills they don't possess. I was worried about the size of the db in the (unlikely) event that the game grows
I'm not following you on the indexes... I have each db indexed by the ID field, and that's normally what I'm accessing it on. Or are you talking about something else?
Re: MySQL efficiency questions
Posted: Tue Jan 05, 2010 12:12 am
by hallsofvallhalla
how many skills you plan on having?
Re: MySQL efficiency questions
Posted: Tue Jan 05, 2010 12:36 am
by OldRod
I've got around 50 so far, between combat skills and trade skills
Re: MySQL efficiency questions
Posted: Tue Jan 05, 2010 6:19 am
by Cayle
Use a bridge table.
Keep your skills that every character has in the main table. Then keep a table that just lists skills. The bridge table would be something along the lines of:
Code: Select all
character varchar(x) NOT NULL, //I forgot how long a UUID is, but you really should track characters by a UID, not by name
skill int NOT NULL, // use the primary key of the skills table, which is probably an auto incremented int
skill_level ont NOT NULL
Then you'll have a join in your select statements that will also give you the skills.
Re: MySQL efficiency questions
Posted: Tue Jan 05, 2010 2:37 pm
by hallsofvallhalla
whoa 50! Nice.. Then i agree with Cayle
Re: MySQL efficiency questions
Posted: Tue Jan 05, 2010 7:17 pm
by OldRod
That's what I was leaning towards, thanks for validating my idea