Does field size affect query time?

Question

My question is about MySQL, but I also wonder how this affects other databases. I have several varchar(255) fields, but my colleague insists if they were varchar(30) - or smaller - then the queries will work faster. I'm not sure, but if I admit it that way.

Answer 1

It depends on the request and the data, but you are probably optimizing too fast to even worry.

For SELECT queries, the operator itself will work just as fast in MySQL, and until the data gets more than on the smaller field, it will be transmitted as quickly as possible. If a smaller field forces you to store information in a smaller space (can you use an extra 225 characters?), You will get fast transfer to other programs.

For INSERT queries, field size is not a problem, but using variable length fields slows down the process. INSERT with fixed-length strings is noticeably faster (at least in MySQL 5.0 and earlier).

As a rule, use the size necessary for the data. If you don’t know if you need 255 characters or 30 characters, you are probably optimizing too soon. Large data fields cause bottleneck? Do you have a program that suffers from database performance issues? First find your bottlenecks, solve the problem with them secondly. I assume that the time difference you are looking at here does not matter for any problem you are trying to solve.

Answer 2

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

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

It’s difficult to talk about all the cases when this will be done, because it depends on how the optimizer processes the query, it depends on the different table structure and indexes that you define, it depends on the type of query, and it even depends on the version of MySQL. as the optimizer is improved with each version.

The short answer is yes, there may be a difference if you use VARCHAR (255) or VARCHAR (30). Therefore, determine the maximum column length according to what you need, and not the “long” length, such as 255, for tradition.

Answer 3

Since you asked about other databases ...

This ABSOLUTELY affects query time.

In Oracle, when data moves from server to client, this is done through a buffer. There is nothing revolutionary. The number of lines placed in this buffer depends on the maximum line size. Say your query returns 4 varchars columns. If the size of the columns is 100 and it should be 10, Oracle will be 10 times smaller than the rows in each sample than otherwise using the right-sized column definitions. This causes the blocks to be reread unnecessarily. This causes more network traffic, more round trips.

In Oracle, you can change the buffer size using SET ARRAYSIZE. Try this, make a request with one size, and then do it again with 10% space. You will see that reading goes up, network trips go up, and productivity decreases. Creating columns is too large, just as making this buffer too small.

But the real reason for exact sized columns is data integrity. You keep bad things. It is as important as performance.

Remember:

• Designing Performance Too Early
• 99% of what you say comes back, you won’t
• Its much simpler, better and cheaper to get something the right time.

Answer 4

If you use only the first 30 characters, then there will be no difference between varchar (30) and varchar (255) (although there would be a difference with varchar (1000), which take an extra byte).

Of course, if you finish using more than 30 characters, it will be slower, since you will have more data to pass to the client, and your indexes will be more.

Answer 5

Anything smaller than VARCHAR (255) will use one byte to preserve its size , so VARCHAR (30) and VARCHAR (255) will not make any difference.

But look if your data is compatible, I mean, it is always the same size, in this case using CHAR would be more useful, because you would not waste time on size information, and your search would be easier to find the data, not the account index.

Even if your data is incompatible, but changes in the let coefficient say one byte, CHAR will be better, because you will still spend one byte with size information.

Answer 6

Very rarely, column widths affect query performance. Of course, if you use larger objects (BLOB, LONGBLOB, TEXT, LONGTEXT), there is a possibility that many data will be pulled out. This may affect performance, but it is not necessary. It really only affects storage. If you need storage size by data type, you can refer to http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html to see the details.

And to repeat: the size of the data warehouse does not necessarily affect the speed of requests. There are many other design considerations that will affect query speed. Designing tables and relationships, key structure, indexes, query and join architecture, etc.

Answer 7

A few years ago, many people suggested using tinytext instead of varchar in MySQL for performance, because finding rows by rows is supposedly faster with a constant row data size. Of course, the MySQL query processing, storage, and indexing algorithms have evolved since then, and now this may not be so strong.

But you are probably optimizing too fast and should not worry about performance at that level.