1

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

8
  • What is the table's storage engine ? – RolandoMySQLDBA Jul 27 '14 at 1:00
  • I use MyISAM and TokuDB, either work work for this table. Neither allows for this. – Alasdair Jul 27 '14 at 4:52
  • No one has anything? – Alasdair Jul 27 '14 at 13:51
  • 1
    Sounds like premature optimization. – mustaccio Jul 27 '14 at 16:40
  • "and VARCHAR is a less efficient storage mechanism" - how do you know that? Did you do any performance testing? – a_horse_with_no_name Jul 27 '14 at 16:51
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 :

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.