2

I'm using MySQL and I'm reading in some places that using CHAR in indexed columns is 20% faster than use VARCHAR. In other places seems that its benefit is only when the table doesn't have any VARCHAR column. Is that true?

The information that I want store is a GUID. Is a better option store the data in a BINARY or in a CHAR if the database uses character set utf8? It's worth convert my data to BINARY every time that I want insert, update or query filtering by the GUID? I prefer faster data access than save disk usage.

Improve this question
4
  • 1
    dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html dev.mysql.com/doc/refman/5.0/en/char.html Check those out. Looks like binary will be a bit faster. Commented Aug 28, 2012 at 14:56
  • Thanks, it seems better use BINARY, but is really better BINARY that VARBINARY? Is it faster convert the GUID to a 16 bytes length BINARY or save it like a 34 bytes BINARY? Commented Aug 28, 2012 at 15:04
  • BINARY is the fastest way to store the GUID information, if you are into micro optimizations. BINARY vs VARBINARY - BINARY is yet again faster because MySQL won't have to compute the actual length it has to save since all BINARY columns will be of the fixed-width. Commented Aug 28, 2012 at 15:14
  • Then, if I have a table with 3 VARCHAR columns and a BINARY column and I do a query filtering by the BINARY column it will be faster? The other columns don't matter? Commented Aug 28, 2012 at 15:21

1 Answer 1

Reset to default
5

Any fixed-width column type will lend itself to faster seeking operations when compared to variable-width types. Unless your table is partitioned, it is also true that the variable-width types can degrade performance even on operations which do not involve them. For consideration, think through the algorithm for how you would iterate all the values in a column when all columns are fixed-width and then when some aren't:

For all-fixed width tables (or partitions), you might use simple pointer arithmetic, where you add the value of the combined data-width of all the columns in the partition each time through the loop.

If there are any variable-width columns, however, you would need to calculate the amount to add to the pointer every iteration, based on the actual on-disk "width" of the columns.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments