Does field size affect query time?

Question

My question is in regards to MySQL, but I also wonder how this affects other databases. I have several fields that are varchar(255) but my coworker insists if they were varchar(30) -- or any smaller size -- then queries would run faster. I'm not so sure, but if it's so I'll admit to it.

Answer 1

Most other answers here are focused on the fact that VARCHAR is stored in a variable-length manner, so it stores the number of bytes of the string you enter on a given row, not the maximum length of the field.

But during queries, there are some circumstances where MySQL converts a VARCHAR into a CHAR -- and hence the size goes up to the maximum length. This happens, for instance, when MySQL creates a temporary table during some JOIN or ORDER BY or GROUP BY operations.

Telling all the cases where it would do this is complicated, because it depends on how the optimizer treats the query, it depends on other table structure and indexes you define, it depends on the type of query, and it even depends on the version of MySQL because the optimizer is improved with each version.

The short answer is yes, it can make a difference whether you use VARCHAR(255) or VARCHAR(30). So define the column maximum length according to what you need, not a "big" length like 255 for the sake of tradition.