MySQL Forums
Forum List  »  Performance

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
2928
April 28, 2016 08:36AM
2565
April 29, 2016 12:41AM
1819
April 30, 2016 11:51PM
1669
May 02, 2016 04:37AM
1448
May 02, 2016 09:14PM
1606
May 03, 2016 02:19AM
Re: text vs varchar
2626
May 03, 2016 04:12AM
1525
May 02, 2016 08:02PM
1740
May 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.