Benchmarks tend to be flawed. Here are some questions that affect the outcome of your test:
How did you measure the sizes?
What are the values in the column? In particular, are they in the same order as the order of insertion?
Did you have the index in place before populating the table? Or did you
ALTER TABLE .. ADD INDEX .. later?
Have you done any
UPDATEs? These tend to mess with BTrees.
Are there trailing spaces in your
CHAR values? (There are some situations where
CHAR removes them -- I don't know about this situation.)
Are all the
CHAR values the same? Or consecutive values have the same first several characters? (There may be optimizations.)
All of those questions have a bearing on the size of a MyISAM index. (The list for InnoDB is similar.)
To answer the question...
A MyISAM index is structured as a BTree in 1KB blocks. Each "record" contains:
- Data (8 bytes for
BIGINT or 13 bytes for
- 5 (by default) byte pointer to the row in the
- Some overhead?
Plus there is some overhead for the BTree.
- If the rows for a BTree are inserted in order, each BTree block (except for the last) will be "full".
- At the other extreme, the rows will be half full due to block splits due to inserting rows at just the wrong time.
- On average, after random inserts and/or deletes, a BTree settles to about 69% full. (
UPDATEs don't matter in your case, since the records seem to be
Now for some math.
BIGINT: 5M rows * (8+5) bytes/row = 65MB. This could easily grow to 81MB with overhead, 69%, etc.
CHAR: 5M * (13+5) = 90MB. So this must be incorrect.
CHAR: 5M * (2+5) = 35MB. This could inflate to 51MB. Suppose you inserted
'x' and MyISAM turned it into
CHAR, working backwards, assuming random insertions: 51MB * 69% = 35MB. Divide by 5M rows. That leaves 2 bytes per
CHAR. If all are the same, or all in a block have the same prefix, then it fits.
CHAR, working backwards, assuming ordered insertion: 51MB/5MB = 10 bytes per row. 5 for pointer leaves 5 for
CHAR. Still possible.
Comments on benchmarking with
- There are very few cases where
CHAR is preferred over
- The database world is moving toward
- It is unrealistic to put
x in all rows.
- If you want to test with effectively random, fixed length, latin1 values:
One more note: There are two ways that MyISAM might [re]build an index:
- Via key_buffer -- this is likely to leave the index at 69% full.
- Via sorting -- this is likely to leave it almost 100% full.
A quick experiment:
ALTER TABLE msisdns_key ENGINE=MyISAM; Watch
SHOW PROCESSLIST; to see which repair method is used. See if some of the indexes shrink.
MyISAM is going away; don't spend too much time on it.
ALTER TABLE .. ADD INDEX .. happens in one of these ways:
- Copy over the table and rebuild all the indexes, or
- Add the new index, not effecting anything else, and not return until finished, or
- Add the new index in the background. The point here is that
ls -l may not be updated yet when you look at it.
FIXED is almost never better than
DYNAMIC, even with MyISAM. The places where it might help:
- Delete a short record, then insert a longer record.
- Update a record, increasing the size of some column(s).
If you have a lot of those and the 'fixed' size is not much bigger than the 'dynamic' size, then maybe fixed is better.
Dynamic is usually better when the average row size is smaller. This is because Smaller --> More cacheable --> Faster.
About the only case for MyISAM is that the disk footprint is smaller than InnoDB. In virtually any other contest, InnoDB meets or exceeds MyISAM.
My 10% Rule of Thumb. When trying to optimize something (usually speed or space), I guesstimate how much a proposed change will help. If less than 10%, I move on in search of 'bigger fish'.
Is it big?. 5M records; 150MB data + index. This is not terribly big. I would rank it at about 80th percentile as MySQL tables go. It should be easily cached in RAM, even if implemented in InnoDB. If it will be several GB, let's look at specific schemas, not "oh, by the way the BIGINT is for phone numbers".
Smallest phone. A 12 digit number can be crammed (without length info) into
BINARY(5). But, I suggest that the messiness in the code makes it not worth the effort. (Perhaps my 10% rule kicks in?)