MySQL Forums :: Performance :: text vs varchar


Advanced Search

Re: text vs varchar
Posted by: hua kai ()
Date: May 03, 2016 04:12AM

Øystein Grøvlen

Thank you very much.


|||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.

From your answer, my understand is
So Why text will not be allocated memory when it's not need, it is also because it is 65535, so it will stored external off-page on disk.
I want to know if this depends on the real data size, for example only stores 'abcd' 4 characters in the text column, will it also stored external off-page


||| 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.

For this, for example:

One user table, with one column 'self description', it's only shown in user profile page, and most of usage is user list, user detail is not much used, I think this column is better to stored in a separated table.

But I also have one case, if the 'self description' is also much used by key-lookup, is this better to store in a separated table? if it is already fetched in user list by list usage, is this will be faster when user access user profile and don't need to load user detail info into memory again?


Another question is, if 'self description' is stored in a separated table, and it will be read when key-lookup, varchar(4000) is enough for this column, is it better to use varchar(4000) than text? is varchar(4000) will save disk space and memory than text? for text, if load into memory, will it allocate 65535 space although actually the column data is only 10 characters?

Options: ReplyQuote


Subject Views Written By Posted
text vs varchar 750 hua kai 04/28/2016 08:36AM
Re: text vs varchar 376 Øystein Grøvlen 04/29/2016 12:41AM
Re: text vs varchar 389 Rick James 04/30/2016 11:51PM
Re: text vs varchar 418 Ø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 545 hua kai 05/03/2016 04:12AM
Re: text vs varchar 340 hua kai 05/02/2016 08:02PM
Re: text vs varchar 460 Ø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.