Does Text have row limit in Mysql?

Question

I have searched on stackoverflow about varchar vs text and studied the documentation.

Iam not sure if i understood it the right way but let me ask you this. I have got Mysql version 5.5.34 and yes the maximum varchar size is 65535 bytes and the text content on my website cannot exceed more than 600 characters, I would prefer using varchar(600) rather than Text.

Since varchar is stored inline with table and faster and when I read all the answers of the question.

rajukoyilandy answer

varchar has 64K row limit

paxdiablo answer

if your row size is approaching 64K, you may want to examine the schema of your database. It's a rare table that needs to be that wide in a properly set up (3NF) database - it's possible, just not very common.

If you want to use more than that, you can use the BLOB or TEXT types. These do not count against the 64K limit of the row

So I have decided to use Text

http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

When I have seen this documentation on the very top it says

 Every table (regardless of storage engine) has maximum row size of 65,535 bytes.
 Storage engines may place additional constraints on this limit, 
 reducing the effective maximum row size

My question is, So no matter what you use Text or Varchar, the row limit is 65kb? or if you use Text there is no row limit? Any help is greatly appreciated.

score 0 · Answer 1 · answered Jan 31 '14 at 04:52

The row size limit applies to the amount of space required to store the row itself, which is not the same as the amount of space required to store the data in the row. From the page you linked to:

BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row.

This is why you can store huge amounts of data in a BLOB or TEXT field without exceeding the row-size limit.

score 0 · Accepted Answer · edited May 23 '17 at 10:25

http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row.

So yes no matter what you use the row limit is 65KB, but since BLOB and TEXT types are stored separately the length of the contents do not count towards the row size, just the initial one to four plus eight bytes do.

This is why you can use LONGBLOB LONGTEXT to store huge files and still be able to have more than just a single LONGBLOB/LONGTEXT column in a table.

As for which is better VARCHAR(600) vs TEXT, with VARCHAR you would be taking up 1802 bytes per column so you still have lots of room to work with when it comes to the row limit. But for performance you would have to do benchmarks as this comment points out inline storage is faster if it frequently used in your queries.

Does Text have row limit in Mysql?

2 Answers2