Why is a BIGINT index larger than a CHAR 13 index?

Question

I have a MySQL DB, with a MyISAM table with 5 million records, and several fields. 1 of the fields is a BIG INT, which I understand to be 8 bytes, and another is a CHAR 13, which I thought was a byte per char.

I notice that when adding a index to each field though, the index file increases by 51MB on the CHAR 13, and by 81MB on the BIGINT.

Based on byte site of each field I expected the BIG INT to be noticeably smaller, but in fact the difference is opposite and bigger than I expected.

Why is that?

CREATE TABLE `msisdns_key` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `key` mediumint(6) unsigned NOT NULL DEFAULT '0',
  `key2` smallint(5) unsigned NOT NULL DEFAULT '0',
  `msisdn_big` bigint(20) unsigned NOT NULL,
  `msisdn_bit` binary(20) NOT NULL,
  `msisdn` char(13) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key` (`key`),
  KEY `key2` (`key2`),
  KEY `msisdn_bit` (`msisdn_bit`(3)),
  KEY `msisdn` (`msisdn`),
  KEY `msisdn_big` (`msisdn_big`)
) ENGINE=MyISAM AUTO_INCREMENT=6745305 DEFAULT CHARSET=latin1

Answer 1

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 DELETEs or 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 CHAR(13) latin1)
• 5 (by default) byte pointer to the row in the .MYD
• 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 FIXED.)

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 VARCHAR(13).

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 CHAR(13):

• There are very few cases where CHAR is preferred over VARCHAR.
• The database world is moving toward utf8mb4, not latin1.
• It is unrealistic to put x in all rows.
• If you want to test with effectively random, fixed length, latin1 values: LEFT(MD5(id), 13)

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