Does anyone have considerable proof that CHAR is faster than VARCHAR ?

views:

answers:

+6 Q:

Does anyone have considerable proof that CHAR is faster than VARCHAR ?

Any benchmark, graph anything at all ? Its all academic and theoretical across the web.

Ok its not the first time that this question has been asked, they all say that using CHAR results in faster selects? I even read in MySQL books, its all the same but I have not come across any benchmark that proves this.

Can any one shed some light over this?

+1 A:

I guess you should pick up the glove and just do it.

Itay Moav 2010-08-04 19:09:19

+2 A:

This is simple logic, to simplify I'll take the example of a CSV file...

would it be faster to search in this line

1231;231;32345;21312;23435552;1231;1;243;211;3525321;44343112;

or this one

12;23;43;54;56;76;54;83;45;91;28;92

as long as you define your length correctly CHAR should be faster as the predefined format help the processing time.

Dominique 2010-08-04 19:13:40

So if what you are saying is true, there is no real speed increase in using CHAR unless, for example a "fullname CHAR(20)" column has all the full names exactly filled with relevant data to 20 chars. Right? or that happens automatically because dead space is added as a trail on these columns?

Akay 2010-08-04 19:18:46

Space is added -- so it's always exactly 20. That's why it's faster.

Lou Franco 2010-08-04 19:20:42

Note that this only applies if ALL fields in the row are fixed-width.

Mchl 2010-08-04 19:32:41

To make the example accurate when you have variable length strings you would have to represent a lot of blank characters for the char. In which case, the varchar is actually easier to read.

Jeff Davis 2010-08-04 19:58:42

We have a wonderful contradiction again :)"In which case, the varchar is actually easier to read."

Akay 2010-08-05 03:37:13

+1 A:

The point is, it is not. Not by itself anyway.

What is true however, is that if there are only fixed width fields in the table, MySQL does not need to perform some calculations to find out the beginnings of each field.

Also there might be a difference for very short fields. If you compare CHAR(1) vs VARCHAR(1), the latter takes twice as much memory as the first (in single byte encodings)

Mchl 2010-08-04 19:14:18

ansaurus

tags:

views:

answers:

Does anyone have considerable proof that CHAR is faster than VARCHAR ?

related questions