Re: Very Large Non-Dynamic Table for Lookups

[Rick James]

At 1B rows, plan on about 100GB of disk space needed. Because of the way BTrees work in InnoDB, and assuming innodb_buffer_pool_size is big enough, plan on 1 disk hit per random fetch.

innodb_buffer_pool_size should be about 70% of available RAM.

The equivalent MyISAM table would take about 35GB; about evenly split between data and index. Even so, it might be slower because it would probably be 2 disk hits most of the time. key_buffer_size is the critical tunable for MyISAM.

Rule of thumb: 100 disk hits per second. Are you OK with 100 lookups per second?

I don't think there have been 1B SSNs in the history of the US. In spite of the numbers being 9 digits, not all combinations are allocated. Perhaps your field includes ids from other countries?

You have the userid within a company, but not the id of the company??