2

Hi Please suggest me should I will be using BLOB or TEXT.I have a requirement where i have to store large amount of text,I have read few post they all have talked about storage but not about about retrieval and inserting speed(if you are about to suggest file system storage but this not project requirement for now).One of them was telling text is faster than BLOB but no concrete proof in documentation,that led me to ask this question please if you guys can help me on the same.

Thanks

ThinkTank
  • 1,737
  • 5
  • 22
  • 36
  • https://stackoverflow.com/q/7071662/14955 – Thilo Feb 26 '19 at 07:38
  • thilo yes clob is in waiting list https://dev.mysql.com/worklog/task/?id=936 it is blob sorry i am editing that thing – ThinkTank Feb 26 '19 at 07:41
  • also https://stackoverflow.com/questions/11624807/what-are-the-differences-between-the-blob-and-text-datatypes-in-mysql?noredirect=1&lq=1 – Thilo Feb 26 '19 at 07:44
  • 1
    yes thilo i have read the same but no documenation proof about being faster than clob/blob neither any reason to make it logical just a statement – ThinkTank Feb 26 '19 at 07:45

2 Answers2

0

Short Answer: TEXT and BLOB will be about the same speed.

Long Answer:

  • Ultimately TEXT and BLOB are just a bunch of bytes. The main cost in inserting/fetching such columns is in finding the row and parsing it. In some situations, the bytes are "off-record", thereby necessitation an extra disk hit (unless cached).
  • There is some CPU processing difference. But this is relatively minor. When moving TEXT between the table and the client, the CHARACTER SET may be changed; this costs something. (Not applicable to BLOB or any %_bin COLLATION.)
  • Another CPU diff: When INSERTing data, the characters are checked for validity. This causes errors if you have bytes that do not match the encoding specified for the client.

Note that the differences say that TEXT may be slightly slower than BLOB.

Here's another technique... If you have some moderately large text fields, it is actually faster than TEXT or BLOB to have the client COMPRESS the text, then store into a BLOB column. The reason why this is likely to be faster is that fewer bytes need to be shoveled around. And the buffer_pool cache is more effective. (Note: I am not talking about ROW_FORMAT=COMPRESSED.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
-1

BLOB and CLOB data might take a long time to retrieve.

TEXT is a data-type for text based input.

More details: https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

Mebin Joe
  • 2,172
  • 4
  • 16
  • 22
  • 2
    Why would a TEXT be faster to retrieve than a BLOB of the same size? – Thilo Feb 26 '19 at 07:43
  • 1
    Because TEXT is stored as plain text(strings) which have a character set and collation where as BLOB datatype stores binary objects which is unstructured and no collation or character set. Hence TEXT will perform faster. – Mebin Joe Feb 26 '19 at 08:03
  • @MebinJoe, I'm fairly sure that isn't completely correct. TEXT and BLOB have exactly the same storage characteristics. The difference is collation as you describe. If a TEXT is retrieved on a different charset than the current connection extra CPU overhead occurs in converting this. Most of the time is however in retrieval in the storage engine. The performance of both should be compariable, but I would expect TEXT to be faster. – danblack Feb 26 '19 at 08:13