MySQL efficiency questions

[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?

[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.

[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?

[hallsofvallhalla]

how many skills you plan on having?

[OldRod]

I've got around 50 so far, between combat skills and trade skills

[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.

[hallsofvallhalla]

whoa 50! Nice.. Then i agree with Cayle

[OldRod]

That's what I was leaning towards, thanks for validating my idea