Recommended SQL Types and Lengths

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)开发者_C百科
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

---

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.

---

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

---

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.