Hey.

If you have a table with a VARCHAR field, you can often improve search performance on it by changing the field to the CHAR type. It has a static length, which allows MySQL to calculate a static row size for all the rows, rather then having to calculate a row size for each row. This allows it to scan the rows faster.

You won't notice any difference tho. If you provide to sort a string, MySQL will automatically pad it with spaces, and then trim them when you fetch it. The only difference is that it will increase the storage space required.

That's not true anymore. VARCHAR used to be maximum 255 chars before 5.0, but now it can be a lot bigger.

So is the char field type faster than text field type? Because that's what I really need to know although it's a bugga it will consume more space for faster performance.

Not really, no. The field itself isn't faster.

What makes it faster is that it will always have the same size for every row. If you create a CHAR(50) column and only put 20 characters into one of the fields, MySQL will still use 50 characters to store that field. It would append 30 spaces to the original 20 and store that. (And then remove the 30 spaces when you fetch the data, so no worries there.)

TEXT and VARCHAR (and VARBINARY) are variable-length fields, which shrink to fit the data each field stores. This means that when MySQL searches the table, it has to calculate the position of the data within the data-file on a per-row basis, whereas when you only use fixed-length fields, it can assume all the rows are the same size and calculate where the data is for the entire table before it starts searching.

So in short, yes, CHAR is faster than VARCHAR, but only if ALL the fields in the table are fixed-size. If you have 3 VARCHAR, TEXT or VARBINARY fields in a table, swapping one out won't change anything. It's all or nothing.

Please note tho, that the performance gain from using this method is usually very minor when compared to other optimization methods, such as properly indexing the fields.

A well placed index can be the difference between counting query speeds in minutes and milliseconds. (True story!)

Did you know that with todays cpu speeds the average computer can calculate the reverse lookup of any hash that contains up to two digits. So if there are >=2 digits being stored in a hash then the computer can calculate the original two digit string within less than a second. Just a thing I learnt when creating a dehasher in Java.

Oops... Wrong thread for this post...

Just a thing I learnt when replying to a MySQL thread