MySql Tinytext vs Varchar vs Char

Question

Building a system that has the potential to get hammered pretty hard with hits and traffic. It's a typical Apache/PHP/MySql setup.

Have build plenty of systems before, but never had a scenario where I really had to make decisions regarding potential scalability of this size. I have dozens of questions regarding building a system of this magniture, but for this particular question, I am trying to decide on what to use as the data type.

Here is the 100ft view:

We have a table which (among other things) has a description field. We have decided to limit it to 255 characters. It will be searchable (ie: show me all entries with description that contains ...). Problem: this table is likely to have millions upon millions of entries at some point (or so we think).

I have not yet figured out the strategy for the search (the MySql LIKE operator is likely to be slow and/or a hog I am guessing for such a large # records), but thats for another SO question. For this question, I am wondering what the pro's and cons are to creating this field as a tinytext, varchar, and char.

I am not a database expert, so any and all commentary is helpful. Thanks -

Answer 1

Use a CHAR.

BLOB's and TEXT's are stored outside the row, so there will be an access penalty to reading them. VARCHAR's are variable length, which saves storage space by could introduce a small access penalty (since the rows aren't all fixed length).

If you create your index properly, however, either VARCHAR or CHAR can be stored entirely in the index, which will make access a lot faster.

See: varchar(255) v tinyblob v tinytext
And: http://213.136.52.31/mysql/540
And: http://forums.mysql.com/read.php?10,254231,254231#msg-254231
And: http://forums.mysql.com/read.php?20,223006,223683#msg-223683

Incidentally, in my experience the MySQL regex operator is a lot faster than LIKE for simple queries (i.e., SELECT ID WHERE SOME_COLUMN REGEX 'search.*'), and obviously more versatile.