php - Using Text vs VarChar

[gameaddict2085]

Hey,
During my sites development, I have used text and varchar fields interchangeably when designing my database tables. I use PDO to access the database via php and wondered if there is any difference between using the two, such as performance etc?

 

[Skizzerz]

Varchar is limited to whatever length you specify when creating the field, e.g. a varchar(256) would be limited to a max of 256 characters long. Text is significantly longer, allowing 65535 characters.

If you do not need to store that much text, or if you know the maximum length of your fields, store it in a varchar instead as then you can ensure that your database doesn't get bloated in case someone manages to store a lot of data in the text field.

In terms of query performance, it is easy to create an index on a varchar, which queries will make use of as long as you are searching for an exact value or a prefix (e.g. where field='exactvalue' or field like 'prefix%'). For text fields, you can still do that but you have to specify a maximum key size.

Alternatively, you can create fulltext indices on both text and varchar, but these are currently only supported in MyISAM tables and not InnoDB.

 

[essellar]

Using TEXT will also result in temporary on-disk tables being created for any query results that return those columns (TEXT and BLOB aren't supported for in-memory tables), so if you don't need to use TEXT, don't do it — it will result in a pretty severe performance penalty. (InnoDB supports fulltext as of MySQL 5.6.x, but we're not there yet here.)

 

[gameaddict2085]

Thanks for your help, looks like I've got to make some severe database changes in the morning