CODEWITHSUNDEEP
mysql
We're All Mad Here
We're All Mad Here

Reputation: 1554

Does varchar affect performance even if not selected?

I have two tables:

table A with columns, INT t_a, INT t_b, INT t_c, INT t_d, VARCHAR t_var
table B with columns, INT t_a, INT t_b, INT t_c, INT t_d, CHAR t_cha

If I select column t_a, will there be any performance difference between table A and table B?

Upvotes: 1

Views: 42

Answers (3)

sqlab
sqlab

Reputation: 6436

In theory there should be a performance penalty when dealing with VARCHARs as you can not work with fixed adress computation.
But in practice this is nowadays not visible.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Yes, but it is quite subtle. I presume that the character fields actually have lengths associated with them.

There is a difference in how the data is stored. The char field will store all the characters in the database, even when they are spaces at the end. The varchar() field will only store the length needed for the fields.

So, if you had a table that contained US state names, then:

create table states (
    stateid int,
    statename char(100)
);

Would occupy something like 100*50 + 100*4 = 5,400 bytes in the database. With a varchar(), the space usage would be much less.

In larger tables, this can increase the number of pages needed to store the data. This additional storage can slow down the query, by some amount. (It could be noticeable on a table with a large number of records and lots of such wasted space).

Upvotes: 2

echo_Me
echo_Me

Reputation: 37233

no difference. between char and varchar when selecting

Upvotes: 2

Related Questions