Re: VARCHAR performance

[Øystein Grøvlen]

Hi,

> What do I have to do to use a VARCHAR bigger than 170 and still avoid temporary
> tables going to disk.

The short answer is: Use MySQL 5.6.15 or later

In MySQL 5.6.15, the maximum length of columns to be stored in MEMORY tmp table is 512 characters. Before that, the maximum length was 512 bytes. This implied that for columns using utf8, the max length was 512/3 = 170 characters. (In other words, if you switch to latin1 you will also be able to use a larger size.)

The reason there is a limit, is that MEMORY storage engine does not handle variable length columns. Hence, long VARCHAR columns can not be efficiently stored.

Hope this helps,

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