One thing have a tendency to worries me in MySQL is the fact that my string fields won't be big enough for that data that should be saved. The PHP project I am presently focusing on will have to store strings, the measures which can vary extremely.
Not understanding how MySQL stores string data, I am wondering if it might be overkill to utilize a bigger data type like
TEXT for strings which will most likely frequently be under 100 figures. Exactly what does MySQL use highly variable data such as this?
Check this out: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html
VARCHAR(M), VARBINARY(M) L + 1 bytes if column values require – 255 bytes, L + 2 bytes if values may require a lot more than 255 bytes
BLOB, TEXT L + 2 bytes, where L < 2^16
So within the worst situation, you are using 1 byte per table cell more when utilizing TEXT.
For indexing: you are able to create a normal index on a TEXT column, however, you must provide a prefix length - e.g.
CREATE INDEX part_of_name ON customer (name(10));
and furthermore, TEXT posts permit you to create and query fulltext indexes if while using MyISAM engine.
However, TEXT posts aren't saved along with the table, so performance could, theoretically, become an problem in some instances (benchmark to determine regarding your specific situation).
In recent versions of MySQL, VARCHAR fields can be very lengthy Up to 65,535 figures based on character set and also the other posts within the table. It's very efficient if you have different length strings. See: http://dev.mysql.com/doc/refman/5.1/en/char.html
If you want longer strings than that, you'll most likely have to suck up and employ TEXT.