One of the things that always worries me in MySQL is that my string fields will not be large enough for the data that need to be stored. The PHP project I'm currently working on will need to store strings, the lengths of which may vary wildly.
Not being familiar with how MySQL stores string data, I'm wondering if it would be overkill to use a larger data type like TEXT for strings that will probably often be less than 100 characters. What does MySQL do with highly variable data like this?
See this: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html
VARCHAR(M), VARBINARY(M) L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes
BLOB, TEXT L + 2 bytes, where L < 2^16
So in the worst case, you're using 1 byte per table cell more when using TEXT.
As for indexing: you can create a normal index on a TEXT column, but you must give a prefix length - e.g.
CREATE INDEX part_of_name ON customer (name(10));
and moreover, TEXT columns allow you to create and query fulltext indexes if using the MyISAM engine.
On the other hand, TEXT columns are not stored together with the table, so performance could, theoretically, become an issue in some cases (benchmark to see about your specific case).
In recent versions of MySQL, VARCHAR fields can be quite long - up to 65,535 characters depending on character set and the other columns in the table. It is very efficient when you have varying length strings. See: http://dev.mysql.com/doc/refman/5.1/en/char.html
If you need longer strings than that, you'll probably just have to suck it up and use TEXT.