Return to Answer

UPDATE

In the question, it was stated that 47 million rows was loaded into mysql. For my suggested table layout, please note the following:

ngram_node is 41 bytes per row: 32 for NGRAM_KEY
8 for the numbers (2 for each SMALLINT)
1 for the internal MyISAM DELETED flag

Each primary key index entry would be 34 bytes
32 for NGRAM_KEY
2 for NGRAM_YEAR

47 million rows X 41 bytes per row = 1.927 billion bytes, about 1.79466 GB.
47 million rows X 34 bytes per index entry = 1.598 billion bytes, about 1.48825 GB.
The MyISAM table consumption should be about a combined total of 3.28291 GB.

The question also mentioned loading 5 billion rows.

5 billion rows X 41 bytes per row = 205 billion bytes, about 190.9211 GB.
5 billion rows X 34 bytes per index entry = 170 billion bytes, about 158.3248 GB.
The MyISAM table consumption should be about a combined total of 349.2459 GB.

Please notice that the growth rate of the space used in the MyISAM table is linear because of the constant-sized primary key. You can now do some planning for disk space based on this.

Please notice I loaded 1-grams, 2-grams, and 3-grams into the same table but you have no clue which MD5 belongs to the ngram. Thus, all ngrams can retrofit into this table. Just remember to insert into ngram_blackhole and the rest is done for you.

If you wish to separate 1-grams, 2-grams, and 3-grams into separate repositories, just create another table, another blackhole, and another stored procedure on the blackhole.

Give it a Try !!!

UPDATE

I saw two typos in my answer. I had ENGINE=BLACKOLE. I changed it to ENGINE=BLACKHOLE. I reran everything. It turned out the same result !!!

Please notice I loaded 1-grams, 2-grams, and 3-grams into the same table but you have no clue which MD5 belongs to the which ngram. Thus, all ngrams can retrofit into this one table. Just remember to insert into the ngram_blackhole table and the rest is done for you.

If you wish to separate 1-grams, 2-grams, and 3-grams into separate repositories, just create another table, another blackhole table, and another trigger on the blackhole table to insert into the other table.

Give it a Try !!!

The reason I chose 256 partitions stems from the fact that MD5 return 16 different characters (all hexadecimal digits). First two bytes is 16 X 16, 256.

The reason I chose 256 partitions stems from the fact that the MD5 function returns 16 distinct characters (all hexadecimal digits). First two bytes is 16 X 16, 256.