Re: text vs varchar
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?