MySQL CHAR(500) – Usage and Considerations

MySQL

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)?

Best Answer

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.

Related Solutions

Mysql – Converting CHAR to BIT

Can you be more specific on "quite large"? In general you're right, you shouldn't just 'do it live' in production with any change. Do you have any kind of QA or test environment you can do a run on first?

This isn't the most sexy ninja one line approach but you could

Add a new bit column
Issue updates setting the new column = if( length(old_column)=16,1,0)
Alter table to drop old column and rename the new column

MySQL – Query All Employees with Sum of Salary > 500

After playing around with this some more, I came up with this query:

SELECT ssn, name, salary FROM (
  SELECT ssn, name, salary, (@total:=@total+salary) as total
  FROM worker, (select @total:=0) t
  ORDER BY salary ASC
) as foo
WHERE (total-salary) <=500

This seems to do what you want on this limited data, but could be very slow on large datasets.