Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

When to use TEXT in mysql instead of VARCHAR [duplicate]

Tags:

mysql

Possible Duplicate:
MySQL: Large VARCHAR vs. TEXT?

Since VARCHAR can have 65k bytes now, when then should TEXT be used instead of VARCHAR?

like image 472
enchance Avatar asked Jul 11 '12 21:07

enchance

People also ask

When should I use VARCHAR or TEXT?

Some Differences Between VARCHAR and TEXT The VAR in VARCHAR means that you can set the max size to anything between 1 and 65,535. TEXT fields have a fixed max size of 65,535 characters. A VARCHAR can be part of an index whereas a TEXT field requires you to specify a prefix length, which can be part of an index.

Should I use TEXT or VARCHAR mysql?

In most circumstances, VARCHAR provides better performance, it's more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.

What is difference between CHAR VARCHAR and TEXT?

CHAR items, which are fixed length, are the fastest to store and retrieve but can waste storage space. VARCHAR, a variable-length string, can be slower to store and retrieve but does not waste storage space. TEXT is a character BLOB that requires more storage space and I/O than the other two.

2 Answers

A long VARCHAR is stored in the same manner as a TEXT/BLOB field in InnoDB.

From storage prospective BLOB, TEXT as well as long VARCHAR are handled same way by Innodb. This is why Innodb manual calls it “long columns” rather than BLOBs.

source

Unless you need to index these columns (in which case VARCHAR is much faster) there is no reason to use VARCHAR over TEXT for long fields - there are some engine specific optimisations in MySQL to tune the data retrieval according to length, and you should use the correct column type to take advantage of these.

In case you're using MyISAM an in-depth discussion on the topic is here.

TEXT and BLOB are stored off the table with the table just having a pointer to the location of the actual storage.

VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable.

According to this test, VARCHAR is about thrice as fast as text.

like image 115
Lion Avatar answered Oct 05 '22 19:10

Lion

Text should be used for really long strings of indeterminate length. Also, queries that return TEXT fields tend to be much slower than their VARCHAR counterparts.

like image 35
TimR Avatar answered Oct 05 '22 19:10

TimR
Sign in to Comment

Related questions

Get sum of MySQL column in PHP
How can I duplicate a database using phpmyadmin?
Python MYSQL update statement
MySQL function to find the number of working days between two dates
MySQL PHP - SELECT WHERE id = array()? [duplicate]
Convert javascript to date object to mysql date format (YYYY-MM-DD)
InnoDB: Attempted to open a previously opened tablespace
MAMP mysql server won't start. No mysql processes are running
Gitlab CI + DinD + MySQL services permission issue
flask_sqlalchemy `pool_pre_ping` only working sometimes
utf8mb4_unicode_ci vs utf8mb4_bin
MYSQL: Index keyword in create table and when to use it
How can I stop a MySQL query if it takes too long?
Use results from one sql query in another where statement (subquery?)
In MySQL, which collation should I choose?
Running AMP (apache mysql php) on Android [closed]
MySQL Nested Select Query?
Is there a way to do an "INSERT...ON DUPLICATE KEY UPDATE" in Zend Framework 1.5?
How do you mock MySQL (without an ORM) in Node.js?
How many databases can I create on a single Amazon RDS instance

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!