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
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 PRIMARY KEY id ); 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) );