0

I'm new to SQL and I'm working on a table that stores account information for a multiplayer game. I'm wondering what is the most efficient way to store a lot of data.

For these three columns, I think, I've already figured it out:

username: VARCHAR(20)
password: VARBINARY(16) for MD5-Hashes
email: VARCHAR(70)

What do you think about this?

Besides that, there will be a lot of more flexible data (savegame data), which I can't really predict. Would it be smart to save this data as XML data in a field of type TEXT? Or is there a better way to save it (using PHP)?

Thanks. Rob

Andriy M
  • 76,112
  • 17
  • 94
  • 154
Mr. MmmSchrubbdt
  • 1
  • For VARCHAR, most DBs use the required number of chars plus the length. Some databases use a single byte for the length if it's less than 255. So specifying a smaller length buys you nothing (except if you want to *enforce* this length). For password, you should IMHO use binary, as its length doesn't vary. – maaartinus May 27 '14 at 21:37
  • Mentioning XML together with the tag space-efficiency should be prohibited. If you don't want to structure your data into multiple tables and care about space, then you should use something less wasteful, e.g., [JSON](http://www.json.org). – maaartinus May 27 '14 at 21:40

3 Answers3

0

A couple of advices :

  • As often as possible, use the smallest possible type (like tinyint instead of just int)
  • If you have a fixen-length string, use a char, and not a varchar (for your md5, for instance)

A note more related to your specific case :

  • 70 characters seems a bit short for an e-mail address
  • Savegame data > I would probably use a TEXT or BLOB field.
Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
0

Don't store xml in a text field. Use a different database engine - one that lets you store arbitrary unstructured data. (Look up nosql)

Ariel
  • 25,995
  • 5
  • 59
  • 69
0

I agree that the email address length is too short when you include the domain names as well. You may want to follow what the maximum Gmail length is.

Also you might be better off saving the game data as a file and saving the path in the database. It is always a bad idea to have large data on the table.

This is especially true when you use indexes in the mySQL innoDB storage engine, where clustered index is used and the row contents are stored in the index. The index would become very large and inefficient.

Also queries involving SELECT/INSERT/UPDATE would become very slow as well, especially the latter 2.

bash-
  • 6,144
  • 10
  • 43
  • 51
  • Thanks for the tip with the eMail length. The problem is that I need access to parts of the data on very low response time and very often. So I don't think its good to save it as a file. – Mr. MmmSchrubbdt Jul 21 '11 at 05:40