Simple Question About MySQL Text Field Types

[webref.eu]

Hi Guys

 

I'm just getting back into coding after taking a break from it.  I want to have a text field that can take up to 1000 characters. What is the best field type for the MySQL field for this?  I can't remember if there are any particular special types.

 

Thanks

[requinix]

With just that description I'd probably do a VARCHAR(1000). VARCHARs are stored inline in the table, like the other normal types like INT and CHAR, so they're pretty fast. You should be extra wary of SELECT *s as you could be pulling this text data and then not using it, which is wasted time and bandwidth.

 

MySQL has size limits on rows, though. 8KB I think. If you needed anything close to that, or had more than one large VARCHAR column, then you may have to switch to TEXT. TEXT (and BLOB) are stored outside the table, which means they're a little slower but obviously you can store much more. VARCHARs may be stored outside too if you ask for a large enough size.

 

Here's a question: is the 1000 arbitrary? Think you might expand it at some point? Does this field represent a free-form input from the user? Are you making sure to enforce a 1000 character limit? Depending on your answers, TEXT may be more appropriate in terms of data modelling, even if it means a bit (a bit) less performance.

Edited February 3, 2015 by requinix