MySQL Forums :: Performance :: text vs varchar


Advanced Search

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

You are correct, but some clarifications:

hua kai Wrote:
-------------------------------------------------------
> Øystein Grøvlen, Rick
>
> Thank you very much for your answer
>
> So here is my conclusion for (innodb):
>
> 1. At storage level, they are same, Short values
> will be stored on same page as rest of row, long
> values will be stored on separate pages.

Correct.

>
> 2. When fetching data from INNODB(no temporary
> table considered), the server will allocate memory
> for VARCHAR even if they are not read in select
> sql, but TEXT will not. So it's better to use TEXT
> instead of VARCHAR if defined in a same table, or
> if you want to use VARCHAR which is seldom used,
> it's better to define in a separated table

As I wrote in another reply, whether it is useful to put VARCHAR in a separate table will depend on your access pattern.

>
> 3. When temporary table case, VARCHAR(n) when n <
> 512, could use tmp table in memory. n > 512 or
> text, it can not use memory, will create tmp table
> on disk

True, but "tmp table on disk" may be a bit misleading. The tmp table will go to a disk-based table (MyISAM before 5.7, InnoDB by default in 5.7), but whether it is actually written to disk will depend on the internal caching in the storage engine.


> Am I correct?

Options: ReplyQuote


Subject Views Written By Posted
text vs varchar 751 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 554 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.