Is there a point in defining varchar-fields that are smaller than 255 bytes? Does it help with indexing or something?
size usage. Only use what you need.
Excuse my ignorance, but isn't the difference between char and varchar that when using the latter, only the amount of characters that equals the length of the string are saved, plus one byte indicating the length of the string. When using char, by contrast, a fixed amount of data is saved, no matter how long the contents may be.
So if you have, for example, a field called email that contains users' email addresses, averaging some 25 characters in length. You could keep this information in a char field with a fixed size of, say 40 characters, but then there would always be fairly much slack. Another thing is that when the user with an email address with one character more than you have defined as length arrives, problems arise.
A better solution would most probably be to use a varchar field with a size of at least 40 characters. My assumption is, therefore, that because only the amount of bytes needed to store the string, plus one byte for size info, are saved in a varchar field, there should be no reason whatsoever not to always use varchar fields of size 255, unless you are using the database itself to restrict the length of the (user) input to the varchar fields (which should be done with the overlying software anyway). Varchar(40) and varchar(255) both take the same amount of space, assuming (in the context of this example) that the strings stored are under 40 characters long.
Because the using of varchar fields with a size less than 255 characters is quite common thing to see in modern database table definitions, I'm just wondering what is the point behind this approach.
There are little/no performance implications with a VARCHAR( 255 ) vs. VARCHAR( 40 ) in theory. I know Oracle/MS SQL/Sybase have no problems with them, but I do not know about MySQL.
However, your data structures should match the business requirements. This way the data structure themselves becomes a data dictionary for others to model their applications (report generation and the like). If you have your email address VARCHAR( 255 ) and another application reads/writes to it, they may inadvertantly not adhere to a 40-character limit, which will break your first application. You would need strict documentation stating "Yeah we have it set to 255 but it MUST be kept under 40" which every application must remember to adhere to. Defining it VARCHAR( 40 ) is self-documenting.
Also, indexes may be needlessly wide on a VARCHAR( 255 ) regardless of whether or not you have 255 characters in there. It depends on your RDBMS and how they physically store their indexes so it would be wise to see if inefficiencies exist in a VARCHAR( 255 ) index over a VARCHAR( 40 ).
Further RDBMS-specific indexing may impose a limit on the number of bytes an index can hold. So if you define your table with username and password of 255 characters and attempt to place an index on those two (since typically authentication is done with username and password) you will not be able to create it if your max index length (as specified by the RDBMS) is 300 bytes.