Return to Answer

Much smaller tables and keys. I also increased the amount of test data. I'll explain the data size metrics later. For now, you can cut and paste this directly into MySQL.

Much smaller tables for year info but much bigger keys to preserve the original ngram. I also increased the amount of test data. You can cut and paste this directly into MySQL.

CAVEAT

Simply remove ROW_FORMAT and it becomes dymanic and compress the ngram_key tables a lot smaller.

USE test
DROP TABLE IF EXISTS ngram_key;
DROP TABLE IF EXISTS ngram_rec;
DROP TABLE IF EXISTS ngram_blk;
CREATE TABLE ngram_key
(
    NGRAM_ID BIGINT NOT NULL AUTO_INCREMENT,
    NGRAM VARCHAR(64) NOT NULL,
    PRIMARY KEY (NGRAM),
    KEY (NGRAM_ID)
) ENGINE=MyISAM ROW_FORMAT=FIXED PARTITION BY KEY(NGRAM) PARTITIONS 256;
CREATE TABLE ngram_rec
(
    NGRAM_ID BIGINT NOT NULL,
    YR SMALLINT NOT NULL,
    MC SMALLINT NOT NULL,
    PC SMALLINT NOT NULL,
    VC SMALLINT NOT NULL,
    PRIMARY KEY (NGRAM_ID,YR)
) ENGINE=MyISAM ROW_FORMAT=FIXED;
CREATE TABLE ngram_blk
(
    NGRAM VARCHAR(64) NOT NULL,
    YR SMALLINT NOT NULL,
    MC SMALLINT NOT NULL,
    PC SMALLINT NOT NULL,
    VC SMALLINT NOT NULL
) ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blk FOR EACH ROW
BEGIN
    DECLARE NEW_ID BIGINT;

    INSERT IGNORE INTO ngram_key (NGRAM) VALUES (NEW.NGRAM);
    SELECT NGRAM_ID INTO NEW_ID FROM ngram_key WHERE NGRAM=NEW.NGRAM;
    INSERT IGNORE INTO ngram_rec VALUES (NEW_ID,NEW.YR,NEW.MC,NEW.PC,NEW.VC);
END; $$
DELIMITER ;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30,vc=vc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30;
SELECT * FROM ngram_key;
SELECT * FROM ngram_rec;
SELECT A.ngram NGram,B.yr Year,B.mc Matches,B.pc Pages,B.vc Volumes FROM 
ngram_key A,ngram_rec B
WHERE A.ngram='rolando angel edwards'
AND A.ngram_id=B.ngram_id;

nrgram_rec has 16 bytes 17 bytes per row
8 bytes for ngram_id (max unsigned value 18446744073709551615 [2^64])
8 bytes for 4 smallints (2 bytes each)
1 byte MyISAM internal delete flag

Index Entry for ngram_rec = 12 bytes
47 million rows X 17 bytes per row = 799 million bytes = 761.98577 MB
47 million rows X 12 bytes per row = 564 million bytes = 537.85231 MB

More metrics later ...

USE test
DROP TABLE IF EXISTS ngram_key;
DROP TABLE IF EXISTS ngram_rec;
DROP TABLE IF EXISTS ngram_blk;
CREATE TABLE ngram_key
(
    NGRAM_ID UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    NGRAM VARCHAR(64) NOT NULL,
    PRIMARY KEY (NGRAM),
    KEY (NGRAM_ID)
) ENGINE=MyISAM ROW_FORMAT=FIXED PARTITION BY KEY(NGRAM) PARTITIONS 256;
CREATE TABLE ngram_rec
(
    NGRAM_ID UNSIGNED BIGINT NOT NULL,
    YR SMALLINT NOT NULL,
    MC SMALLINT NOT NULL,
    PC SMALLINT NOT NULL,
    VC SMALLINT NOT NULL,
    PRIMARY KEY (NGRAM_ID,YR)
) ENGINE=MyISAM ROW_FORMAT=FIXED;
CREATE TABLE ngram_blk
(
    NGRAM VARCHAR(64) NOT NULL,
    YR SMALLINT NOT NULL,
    MC SMALLINT NOT NULL,
    PC SMALLINT NOT NULL,
    VC SMALLINT NOT NULL
) ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blk FOR EACH ROW
BEGIN
    DECLARE NEW_ID BIGINT;

    INSERT IGNORE INTO ngram_key (NGRAM) VALUES (NEW.NGRAM);
    SELECT NGRAM_ID INTO NEW_ID FROM ngram_key WHERE NGRAM=NEW.NGRAM;
    INSERT IGNORE INTO ngram_rec VALUES (NEW_ID,NEW.YR,NEW.MC,NEW.PC,NEW.VC);
END; $$
DELIMITER ;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30,vc=vc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30;
SELECT * FROM ngram_key;
SELECT * FROM ngram_rec;
SELECT A.ngram NGram,B.yr Year,B.mc Matches,B.pc Pages,B.vc Volumes FROM 
ngram_key A,ngram_rec B
WHERE A.ngram='rolando angel edwards'
AND A.ngram_id=B.ngram_id;

nrgram_rec has 17 bytes per row
8 bytes for ngram_id (max unsigned value 18446744073709551615 [2^64 - 1])
8 bytes for 4 smallints (2 bytes each)
1 byte MyISAM internal delete flag

Index Entry for ngram_rec = 10 bytes (8 (ngram_id) + 2 (yr))

47 million rows X 17 bytes per row = 0799 million bytes = 761.98577 MB
47 million rows X 12 bytes per row = 0564 million bytes = 537.85231 MB
47 million rows X 29 bytes per row = 1363 million bytes = 1.269393 GB

5 billion rows X 17 bytes per row = 085 billion bytes = 079.1624 GB
5 billion rows X 12 bytes per row = 060 billion bytes = 055.8793 GB
5 billion rows X 29 bytes per row = 145 billion bytes = 135.0417 GB

ngram_key has 73 bytes 64 bytes for ngram (ROW_FORMAT=FIXED set varchar to char) 8 bytes for ngram_id 1 byte MyISAM internal delete flag

2 Index Entries for ngram_key = 64 bytes + 8 bytes = 72 bytes

47 million rows X 073 bytes per row = 3431 million bytes = 3.1954 GB
47 million rows X 072 bytes per row = 3384 million bytes = 3.1515 GB
47 million rows X 145 bytes per row = 6815 million bytes = 6.3469 GB

5 billion rows X 073 bytes per row = 365 billion bytes = 339.9327 GB
5 billion rows X 072 bytes per row = 360 billion bytes = 335.2761 GB
5 billion rows X 145 bytes per row = 725 billion bytes = 675.2088 GB

Much smaller tables and keys. I also increased the amount of test data. I'll explain the data size metrics later. For now, you can cut and paste this directly into MySQL. Stay tuned...

Much smaller tables and keys. I also increased the amount of test data. I'll explain the data size metrics later. For now, you can cut and paste this directly into MySQL.

DiskSpace Metrics

nrgram_rec has 16 bytes 17 bytes per row
8 bytes for ngram_id (max unsigned value 18446744073709551615 [2^64])
8 bytes for 4 smallints (2 bytes each)
1 byte MyISAM internal delete flag

Index Entry for ngram_rec = 12 bytes
47 million rows X 17 bytes per row = 799 million bytes = 761.98577 MB
47 million rows X 12 bytes per row = 564 million bytes = 537.85231 MB

More metrics later ...