[Resolved] MySQL Field Types

jenni

I was wondering if anyone had a good reference for choosing the correct field type when setting up a MySQL database. I know what each type does, but now I am interested in optimization. For example...what is the best field type for an email address or a phone number, etc.

rachel2004

To give you a general idea:

first consider what type of data you will be storing. For e-mail, it would be text.
what is the maximum reasonable size that you should set for the an e-mail? Numbers here can vary (although domain names have a cap of 255 characters--someone correct me if I'm wrong). However, the chances that you'll come across an e-mail address that will require 255 chars is not that likely.

Use your db design in a "test" environment for a while so you can come across situations that maybe you hadn't thought about before eventually easing it into production use.

When all else fails, you can use the ALTER TABLE command in MySQL:

http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html

Drakla

And if you're indexing fields you don't always have to have the index at the same length as the field name, say you had email addresses that the field was 80 characters wide on, if you were doing searches that would generally hit the results in about 8 characters of it then you could just do:

CREATE INDEX emailindex ON users ( emailaddress(8) )

Which would speed up queries.

jenni

Thank you both for replying. I really should have put this in the best practices forum. Drakla, could you give a little more info on the indexing?

Drakla

Have a quick look at this tutorial first, as it's one that made me understand indexes when I got started

http://www.databasejournal.com/features/mysql/article.php/1382791

I'll probably just confuse you with my disjointed nonsense if I try explaining.

jenni

That tutorial rocks! Thank you so much!