Many thanks for everyone's comments
Had a few minutes so thought I would do a quick test to see how the db size changed based on different setups
- Created a new db, added a table with a single 6 char text field, unindexed and compacted - 356kb
- added 1m random 6 character random text records 19,068kb
- compact/repaired 19,056kb
- added a non unique index - took about a second - 30,688kb
- compact/repaired 30.684kb
- change field size from 6 to 200 - took several seconds - 54,740kb
- compact/repaired 30,684kb
- removed index 30688kb
- compact/repaired 19052 kb
- changed field size from 200 to 100 and added back the index - took less than a second - 43,112kb
- compact/repaired 30.684kb
Summary
file sizes at steps 3 and 5 indicate records are added consecutively as would be expected, so not much compacting to do
step 3 - simple maths based on the 18,700 file size increase indicates a 6 character string takes 18 or 19 bytes. Rule is (I think) 10bytes +2 bytes per char=22 bytes - maths is pretty basic but file size increase is broadly in line with expectation
step 5 - increase in size from step 3 to step 5 is an increase of 11,628 implying the index takes less space than the actual value even tho' a pointer is also required. Maths implies only the characters are stored in the index, not the 10 byte overhead.
step 6 - confirms the comments about the index being rebuilt if the field size is changed (increase in size 24,056)
step 7 - demonstrates indexes would appear to take up the same space regardless of the field size
step 9 - restored to original non-indexed state - same size as for 6 character limit - to be expected
step 10 - increase in size 12428 - approx 1/2 of the increase for a 200 char index - to be expected.
step 11 - back to the same situation at step 7
Conclusions
- If you change the field size on an indexed field, do a compact/repair to recover the space
- Indexes take up space related to the field value, not the specified size
- Indexes (in respect of short text) take up less space that the field value, (other datatypes not tested)
Not tested
This test in based on 1m records each with a 6 char value. Not tested to see if the index is adjusted to the max actual field length when there are varying field lengths. This thought comes because the creation of the 100 char index was significantly faster than when the 200 char index was created. I might have expected around 50% of the time, not 5 or 10%. Alternative speculation is there is a time overhead on first creation of an index