2

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

| improve this answer | |
  • This just test data, I'm updating all the records in order with the new data, I then measure the size of the files with "ls -l". If it doesn't make sense I then try deleting the index, repair/optimize, then creating it again. These ones are staying the same. The data is test data, but not completely random, it's designed to to mimic real world data. It's phone numbers so the there are a lot of the same prefixes, the first 7 chars of the 13 are a mix of 10 prefix possibilities, rest are random between 100000 and 999999. – GemmaB89 Jan 22 '18 at 18:43
  • The the similar prefixes seems like a perfect explanation for the smaller size of the CHAR field. Given that a lot of the random sections will also give the same prefixes, around 4 to 5 chars per record on average sounds about right. So thanks for clearing that up! Couple of questions: My understanding is that char instead of varchar, along with other fixed length fields, gives a performance boost, hence why I am using it. Is that not that case, or is the boost negligable? When you say MyISAM is going away, is it being replaced with Inno or something else? – GemmaB89 Jan 22 '18 at 18:43
  • The bigint I am using is another way of representing the phone numbers, and it does give a small speed increase, maybe 5%, but not sure that's worth the increase in size. Any recommendatons on what I could do to either speed up searches or decrease size of data/index? Thanks again :) – GemmaB89 Jan 22 '18 at 18:43
  • @GemmaB89 - I addressed many of your Comments via additions to my Answer. – Rick James Jan 22 '18 at 22:14
  • @GemmaB89 - Well, the code is Open. Someone (not me) could wade through to see if the prefixing only applies to char, not binary/int/datetime/etc. Why is 80MB a problem in today's sized machines? – Rick James Jan 23 '18 at 19:02

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.