Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Assuming the data is the same either way, does the maximum length of the VARCHAR affect the space usage of a secondary index? Does InnoDB use fixed length records for indexes?

Assume that we're talking about MySQL 5.1, with the InnoDB COMPRESSED table format and that the field in question is defined as a VARCHAR with some length less than or equal to 255 (so that it uses only one byte for the offset).

Here is the use case:

I have a server with a very large table (several gigabytes). One of the fields is currently VARCHAR(7). We need it a little longer and we are thinking of making it VARCHAR(255), but we are worried that it bloat the index.

share|improve this question

closed as off topic by Shane Madden, Michael Hampton, Ward, Scott Pack, Iain Sep 24 '12 at 21:19

Questions on Server Fault are expected to relate to server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

up vote 1 down vote accepted

Nope, innodb doesn't fully expand varchar fields when used in secondary indices.

Yves Trudeau has a good blog entry from a couple years ago that proves this explicitly by digging into innodb pages with the innodb recovery tool: http://www.bigdbahead.com/?p=150

share|improve this answer

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