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

mysql

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

VARCHAR needs less memory overhead, but they will usually be fully read in memory, so at the end VARCHAR might still use more memory. They are both different. You use BLOB to store binary data like an image, audio and other multimedia data. and VARCHAR to store text of any size up to the limit.

Johan

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.

Whether or not this is better depends on your data access patterns.

Which separate file could that be?

Blobs aren't save in a separate file. But they are stored in a separate physical location from the rest of the columns.

Note that this doesn't only depend on frequency of access, but also what operations are being performed on the data. For example, any query requiring a table scan (which is generally bad anyway), but not the column of text will be made worse by the larger volume of data scanned.

I also suspect that filesorts not using this column may be more efficient if the data is stored off-page although I'm not sure the query optimizer is smart enough not to pull this data.

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

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US