MySQL - Is using TEXT for potentially small strings overkill?

Question

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?

Answer 1

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).