varchar(255) vs tinytext/tinyblob and varchar(65535) vs blob/text

Mysql

Mysql Problem Overview


By definition:

>VARCHAR: The range of Length is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. x+1 bytes
>TINYBLOB, TINYTEXT: A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters x+1 bytes

So based on this, I creaate the following table:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  `lastname` tinytext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Or is it better to create a varchar or tinytext and why?

Is it the same for:

>VARCHAR: The range of Length is > 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. x+2 bytes
>BLOB, TEXT A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters x+2 bytes

Mysql Solutions


Solution 1 - Mysql

In this case varchar is better.

Note that varchar can be from 1 to 65535 chars.

>Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section E.7.4, “Table Column-Count and Row-Size Limits”.

Blobs are saved in a separate section of the file.
They require an extra fileread to include in the data.
For this reason varchar is fetched much faster.

If you have a large blob that you access infrequently, than a blob makes more sense.
Storing the blob data in a separate (part of the) file allows your core data file to be smaller and thus be fetched quicker.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionTech4WilcoView Question on Stackoverflow
Solution 1 - MysqlJohanView Answer on Stackoverflow