Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

At which point does MySQL start treating VARCHAR cols like TEXT cols?

I'm aware that since MySQL 5, VARCHAR can have a length of up to 65,000. VARCHAR is stored inline, which means faster retrievals, as opposed to TEXT, which is stored outside of the table. That said, the documentation states that MySQL will treat LONG VARCHAR exactly TEXT.

According to this Source:

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.

When does MySQL start treating VARCHAR like TEXT? At what character count does MySQL make this distinction, and VARCHAR stops getting stored inline?

like image 837
Mohamad Avatar asked Jul 01 '11 14:07

Mohamad


1 Answers

Short answer: A "long" VARCHAR is a normal VARCHAR and will be inline.

MySQL won't magically start treating a straight VARCHAR as a text type. It'll always be stored inline. With 5.0.3, the upper limit for VARCHARs was relaxed to 65,535 bytes. They also take up 2 bytes of header if over 255 characters. This limit is still applied to the maximum row size of 65,535 bytes. A LONG VARCHAR is actually a different type which backcompats to MEDIUMTEXT.

See: http://dev.mysql.com/doc/refman/5.0/en/char.html and http://dev.mysql.com/doc/refman/5.0/en/blob.html

like image 52
SoftArtisans Avatar answered Nov 11 '22 03:11

SoftArtisans