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 b开发者_运维百科enchmark that proves this.

Can any one shed some light over this?

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.

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)

CHAR will be faster as it is fixed length. For example CHAR(10) and VARCHAR(10) CHAR(10) is a fixed-length string of 10 while VARCHAR is a variable-length string with maximum length of 10.

VARCHAR(10)

 {Index} (Length) [String]
 -------------------------
 {0} (8) [AAAAAAAA]
 {1} (5) [BBBBB]
 {2} (3) [CCC]
 {3} (7) [DDDDDDD]
 {4} (2) [EE]
 {5} (4) [FFFF]

CHAR(10)

 {Index} (Length) [String]
 -------------------------
 {0} (10) [AAAAAAAA  ]
 {1} (10) [BBBBB     ]
 {2} (10) [CCC       ]
 {3} (10) [DDDDDDD   ]
 {4} (10) [EE        ]
 {5} (10) [FFFF      ]

So imagine you have a table with 1,000,000 records and you need to get a record at offset 500,000.

CHAR - database engine would have to do multiply 500,000 x 10 = offset is 5,000,000.

VARCHAR - database engine would have to get each row length and sum them all 5 + 8 + 9 + 3 + 2 + 4... 500,000 times to get offset

CHAR padding is x00 which basically is end of the string so CHAR(10) = [A,A,A,A,A,00,00,00,00,00] is "AAAAA".

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