When I define columns in MySQL I generally use int, varchar(255), text and the occasional enum for boolean. What are the benifits of accurately specifying column types rather than just leaving them at maximum?
For example a password field encoded as MD5 will never exceed 32 characters so is there a tangible performance gain from using varchar(32) over varchar(255)?
I'm not expert, but I think you save some space if you declare password to be varchar(32), aswell as other types.
For integral types, you are saving space in the row if you use a one byte/two byte integer instead of a four or eight byte integer when appropriate. This does have tangible effects on tables with large amounts of data since more rows will fit on a page.
For character types, I'm really not sure if it would make a difference or not if you're in control of both the db and code. But consider a division of labor where the database designer tells the software developer, "hey, don't let people put more than 100 characters in this field, it would be a total waste". You're enforcing that by setting the limit, where if it was varchar(255) people may well waste the space.
Per the manual, aVARCHAR(N) of a given actual length takes the same amount of space for any N up to 255, so using 32 saves no space. But there's an advantage of clarity and readability of your schema in using a type that best represents what the actual data must be like.
Modern RDBMSs do not really give you better performance on a 255 character column over a 50 character column due to column width.
On SQL Server, I take care to put my name, address, etc as NVarChar so I can internationalize when required. I have phone number storage capable of storing more than 10 digits as standard in the USA.
I would agree with what Clyde says, but your example of a password is not a particularly good one. Since an MD5 sum will ALWAYS be 32 characters, you could use a CHAR(32) instead of a VARCHAR(32) which would be faster/more efficient in many cases.
There is a great benefit if you can have the row size fixed. Then indexing is super-fast. You will have to use fixed column types, like int, and char(some size) for text.
If you will have variable row size anyway, and your table will have less than 100k records, you shouldn't worry to much about optimization. Text instead of varchar is more flexible.
Concerning data validation, I think you should enforce this in your business code/validation.