A table has about 300M records. Each has an unique ID that starts with 13 to 14 digits (with the first digit on the left being either 1 or 2), and is optionally followed by a string of up to 7 ASCII chars. Two examples are 1450123410123 and 21347854123024Asd_uhA.

Currently, I'm storing this field as a VARCHAR with a PRIMARY KEY, and am worried about the memory usage of the index. I'm thinking of splitting this field into two columns, a BIGINT and a VARCHAR. Will the PRIMARY KEY created as a combination of these two columns consume less memory?

Update: Here are the two possibilities.

Please note that I care about inserts and updates. I will have only one huge select to export the data after a long time.

CREATE TABLE without_split (
  id varchar(21) CHARSET=ascii COLLATE ascii_bin NOT NULL,
  content text NOT NULL

CREATE TABLE with_split (
  id_prefix bigint unsigned NOT NULL,
  id_suffix varchar(7) CHARSET=ascii COLLATE ascii_bin NOT NULL,
  content text NOT NULL
  PRIMARY KEY (id_prefix, id_suffix)

1 Answer 1


MyISAM! Shame. Switch to InnoDB.

Don't worry about that PRIMARY KEY; it is too much hassle to split it up, etc. But do make sure it is the 'appropriate' character set:


If it is case sensitive, then add on COLLATE ascii_bin.

Yes, there is a memory and disk penalty. But there are tradeoffs with speed, simplicity, etc.

(If it were 100 characters long, I might give a different answer.)

Numbers for MyISAM

In MyISAM, assuming the strings are half length on average:
PRIMARY KEY(decimal_14_0, varchar_7) needs 2*(7+(1+3.5)) bytes.
PRIMARY KEY(varchar_21) needs 2*(1+(13.5+3.5))

  • 2* because PK fields stored with data and with index;
  • 7 bytes for DECIMAL(14,0);
  • 1=length field for VARCHAR;
  • 3.5(?)=avg bytes for up-to-7-char suffix;
  • 13.5(?) for avg # digits in prefix.

After cancelling out things, the diff is 2*(13.5-7)=13 bytes smaller per row (data+pk) for DECIMAL+VARCHAR. 300M*13=4GB.

(InnoDB computation is in Comments.)

  • Thanks! This is the configuration I actually have. I'm willing to trade simplicity for memory. Will the split free up some memory?
    – mossaab
    Feb 4, 2016 at 13:33
  • InnoDB is 2x-3x more bulky on disk than MyISAM. InnoDB's secondary keys implicitly include a copy of the PRIMARY KEY. Etc. If you would like a longer answer, please provide two SHOW CREATE TABLE -- split, and not split. I will explain the diffs, and estimate the space diff. And supply a typical SELECT.
    – Rick James
    Feb 4, 2016 at 16:37
  • I've updated the question.
    – mossaab
    Feb 4, 2016 at 17:52
  • Need more -- MyISAM vs InnoDB? Any secondary indexes? Typical SELECT? And if you are going to squeeze all you can out, use DECIMAL(14,0) (7 bytes) instead of BIGINT (8 bytes).
    – Rick James
    Feb 6, 2016 at 0:54
  • I can switch to InnoDB if that will reduce memory usage. No secondary index. I will have a single SELECT * at the end of the process. DECIMAL(14,0) is a nice idea actually. I'd love to have it in the final answer.
    – mossaab
    Feb 6, 2016 at 4:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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