Reading mysql rows faster SELECT * fixed vs variable

Setup: id=INT, primary key, auto increment. Then 20 text columns, either varchar or char (see question below).

If i'm reading an ENTIRE single row of mysql data in a query (SELECT *), Is it faster if I use char rather than var开发者_如何学JAVAchar, as I assume mysql will not need to retrieve the individual column lengths data? (Looking to see if there is a different for the SELECT * query)

I am not convinced that using char is faster than varchar, because somewhere you have to trim the strings anyway before displaying them (or cart around hundreds and thousands of spaces around in memory), not to mention the time spent packing and unpacking all those spaces for network transfer.

Just go for clarity of intent.

If you want something to fix in your program, start with select *.

VARCHAR should be faster the larger the column is specified, since CHAR consumes all characters that are declared.

E.g. VARCHAR(1) won't make a difference to CHAR(1), you probably won't see the benefit with length 10 neither. If you use lengths like 1000 there should be a difference.

Without knowing more about your application, this is really hard to answer. In some regards, CHAR is more efficient, as the engine always knows the size of any data in the columns. However, you're also taking up space you may not need (CHAR(50), when most rows will have only 4-5 characters in that column).

As Blindy said, start with your query before you worry about engine optimizations.

Selects will be faster with "char" since the size is fixed instead of variable. The downside is that your database will take up more space and trailing white spaces will not be preserved (if you need them to be). I would suggest profiling your app using varchar and if it is a performance problem switch over to using char.