MySQL Forums :: Performance :: text vs varchar


Advanced Search

Re: text vs varchar
Posted by: Øystein Grøvlen ()
Date: May 03, 2016 02:19AM

From the reference manual (http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html):

"If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page."

Default page size is 16k. Hence, if a column value is more than 8k, it will definitely be stored off-page. Also, smaller values may be stored off-page if total row size is larger than 8k. Note that the number of bytes used per character depends on the character set used. If stored off-page, it will not be read when during table scans unless the column is actually needed.

Whether it is useful to store columns in a separate table will depend on your access pattern. If you do a lot of table scans or range scans on PRIMARY key, you would want the table to be as small as possible. If you do mostly key look-ups, table size does not matter than much. If you store the columns in a separate table, you will have extra overhead when fetching these columns since a join will be needed.

Hope this helps,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

Options: ReplyQuote


Subject Views Written By Posted
text vs varchar 752 hua kai 04/28/2016 08:36AM
Re: text vs varchar 377 Øystein Grøvlen 04/29/2016 12:41AM
Re: text vs varchar 390 Rick James 04/30/2016 11:51PM
Re: text vs varchar 419 Øystein Grøvlen 05/02/2016 04:37AM
Re: text vs varchar 358 hua kai 05/02/2016 09:14PM
Re: text vs varchar 366 Øystein Grøvlen 05/03/2016 02:19AM
Re: text vs varchar 557 hua kai 05/03/2016 04:12AM
Re: text vs varchar 340 hua kai 05/02/2016 08:02PM
Re: text vs varchar 461 Øystein Grøvlen 05/03/2016 02:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.