CODEWITHSUNDEEP
mysqldatabasesqlite
Roman
Roman

Reputation: 617

Most space efficient way to store 200 million records?

I have this type of record:

  1. name varchar(128)
  2. description varchar(64)
  3. field varchar(32)

Combination of (name, description) needs to be unique and name needs to be indexed for fast search/retrieval. Updates are infrequent, but large for example I might add 1 million records at a time, but there plenty of reads throughout the day.

I have over 200 million records like these, possibly becoming 300 in the future, when I tried adding it to MySQL, DB has grown huge I'm looking at 200GB+ with all the indices and etc. Is there a space efficient way to structure this data. Any DB tech is fine with me, as long it does not need lots of ram and uses less disk.

200 million * (128+64+32) = ~50GB of data. Don't know how MySQL stores data, but I'm guessing it creates an index for unique key(124+64), so already we are talking about 50GB + ~index 40Gb of data. There is some other overhead, possibly due to fragmentation as suggested below.

Thanks, in advance!

Upvotes: 1

Views: 1532

Answers (1)

CL.
CL.

Reputation: 180080

Both MySQL and SQLite store text values in dynamically-sized records; table or index entries are not padded.

You can avoid the space needed for the index for the UNIQUE constraint by making the name/description columns the primary key and thus using a clustered index (requires InnoDB in MySQL, or WITHOUT ROWID in SQLite):

CREATE TABLE MySQLTable(
    name        VARCHAR(128),
    description VARCHAR(64),
    field       VARCHAR(32),
    PRIMARY KEY(name, description)
) ENGINE = InnoDB;

CREATE TABLE SQLiteTable(
    name        VARCHAR(128),  -- SQLite ignores the limits
    description VARCHAR(64),
    field       VARCHAR(32),
    PRIMARY KEY(name, description)
) WITHOUT ROWID;

Upvotes: 1

Related Questions