MySQL CHAR(500)?

Question

I have a table in which one of the fields is always going to be exactly 500 ASCII characters.

I'd like it to be saved inline in the database and be as efficient as possible, as it will be a big table, i.e. millions of rows, and frequently accessed.

So I'd like to make the field CHAR(500) but it seems MySQL does not allow this. It will allow VARCHAR(500) but that seems like a waste because it is always going to be 500 characters and VARCHAR is a less efficient storage mechanism since it adds lots of additional calculations during seeking.

Is there any workaround to get the equivalent of CHAR(500)?

Answer 1

What I am about to suggest may only possible to MyISAM (maybe InnoDB)

While leaving the VARCHAR(500) column as is, change the row format to fixed by doing

ALTER TABLE mytable ROW_FORMAT=Fixed;

When you do this, all VARCHAR fields are expanded to the size given. That could possibly increase read I/O performance. I have discussed this many times in the DBA StackExchange :

• Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes
• May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
• Jan 03, 2012 : Optimized my.cnf for high-end and busy server
• May 02, 2013 : Varchar index - will hashing value make it faster?

Just brace yourself for MyISAM tables growing 60-100% in size.

EPILOGUE

In your case, since all values are exactly 500 characters, you may not get back the one or two bytes for length management per row. Notwithstanding, you might see a slight performance increase.

Give it a Try (although I cannot promise any improvements unless you at least try) !!!

CAVEAT

I do not know if ROW_FORMAT=Fixed works for TokuDB. Even if it did, It would get compressed and would have no increase in retrieval performance. If any TokuDB experts have a different viewpoint, please post it as a separate answer.