102

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

MikeSchinkel
  • 4,947
  • 4
  • 38
  • 46
Tech4Wilco
  • 6,740
  • 5
  • 46
  • 81
  • 1
    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. – Book Of Zeus Sep 10 '20 at 03:05

1 Answers1

15

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.

Johan
  • 74,508
  • 24
  • 191
  • 319
  • 1
    Whether or not this is better depends on your data access patterns. – Michael Mior Oct 13 '11 at 14:41
  • @MichaelMior, in this case varchar is better, in the general case it depends, updated the answer – Johan Oct 13 '11 at 14:46
  • 1
    Which separate file could that be? – glglgl Oct 13 '11 at 16:09
  • 1
    Blobs aren't save in a separate file. But they are stored in a separate physical location from the rest of the columns. – Michael Mior Oct 13 '11 at 16:47
  • 2
    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. – Michael Mior Oct 13 '11 at 16:52
  • 1
    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. – Michael Mior Oct 13 '11 at 16:53
  • 1
    I stay away from 'varchar' and prefer 'text' if text is like a 'note box' inside a form as I ran into problems in the past. Although it has the 255 threshold, getting closer to 255 is never guaranteed full 255 privileges. It can be shorter sometimes and that is when the error messages occur. This comment is for the older versions of MySQL.
    – Mugé Nov 05 '14 at 07:59
  • As to the original poster's question on "lastname -> tinytext", certainly 'varchar' would be preferred over 'text' as outlined by @Book of Zeus, that it is of ISO standard and easier to get query results. – Mugé Nov 05 '14 at 08:09
  • @MichaelMior, Your 3 comments does call for a citation needed. – Pacerier Apr 16 '15 at 10:50
  • @Pacerier How exactly BLOBs are stored is dependent on the storage engine, but here's links for [MyISAM](http://dev.mysql.com/doc/refman/5.7/en/dynamic-format.html) and [InnoDB](http://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-overview.html). – Michael Mior Apr 16 '15 at 13:30
  • There is no "better". For text data use TEXT unless you want to use it in a WHERE (LIKE) clause or want to create an index on it. – theking2 Jan 23 '23 at 15:08