MySQL: VARCHAR(1024) vs VARCHAR(512)

Question

In MySQL what is the difference between VARCHAR(1024) and VARCHAR(512)? If my item will never be more than 512 characters, what do I lose by using VARCHAR(1024)?

Answer 1

Don't know where you got that from, but it's not possible to create a table with varchar without specifying the length. It results in a syntax error. So your question is obsolete.

UPDATE:

Nothing. Varchar is as the name implies a datatype of variable length, at least to the maximum length you specified when creating the table. This means, that in a varchar column for each row one additional byte is used to store how long the string in the row actually is. So the difference between varchar(1024) and varchar(512) is, that your data gets truncated when you try to insert more than 1024 or 512 bytes. Note: bytes, not characters. How much bytes each character uses is dependent on the character set you're using.

Answer 2

There is a actually a difference. And it can have a big performance impact if you manipulate big data. If a temporary table is used, the records on disk will take the full length indicated instead of the variable length. A high value will slow down the request even more in that case. Temporary tables can occur for various reasons (such as memory full, or some combinations of group by /order by).