Will MySQL be effective when selecting a row from a table with billions of records? Note: this is a hypothetical scenario

Lets say that:


I'm running MySQL, InnoDB engine, UTF-8 encoding
I have a table with 50+ billions of rows
The tables primary key is a BIGINT
The table also has 4 other columns, 3 of which are indexed (a BIGINT, TINYINT, and a VARCHAR(20)) ... (the unindexed column is type TEXT)


My questions are:


How fast would the queries be when selecting a row via primary key, or via indexed columns? What kind of hardware/server/s would be needed in order to make these queries execute faster than 0.1s?
Should I be using some other storage engine for this?
Let's do a hypothetical answer to a hypothetical question (all of this are ball-park calculations, I do not intend to be very exact, but to have a big picture):

Your table, at the bare minimum, will have a storage requirements of:

50 000 000 000*(8 + 8 + 1 + 1 + 20*3) / 1024^4 ~ 4TB


I do not have into account the TEXT because, even if it can be inline, if it is too big it goes off-page (so funnily enough, it won't be a problem or a performance penalty unless it is selected or modified).

The secondary indexes (I do not have into account the primary key, as the data is clustered around it, but obviously, it will take even more space than just the data) will take at least:

50 000 000 000 * ( (8 + 8) + (8 + 1) + (8 + 1 + 20 *3) ) ~ 4TB


And that is being too optimistic.

As that cannot be realistically into the main memory (largest deployments of traditional SMP architectures can only integrate around 4TB, I do not consider other architectures with its own problems), a single access though primary key may require ~25 theoretical disk hits (double it for secondary keys), which with fast enough disks (a huge RAID of SSDs) and disabling large read-ahead caching to the buffer pool and other tweaks, in a single-thread read-only load: why not?

The problem is that InnoDB was designed assuming that the buffer pool being able to cache at least the most used/upper parts of the primary key, which -assuming you can select any row of the table- would create an excessive amount of traffic between the main memory and disks. With the more and more popular flash drives, some parameters of InnoDB have been tuned to allow faster and faster disk access.

In fact, in reality your biggest problem would be inserting. You probably won't be able to have such a tall table due to the increasing performance penalty of checking for uniqueness on the PK for every row inserted. That is why InnoDB works very well when the working set is mainly on the buffer pool, but not when it goes beyond that. Additionally, structures like the change buffer, transaction log etc. would be easy overloaded on insertion. You can see a big slowdown on the blue line once a threshold is surpassed:


Note: the previous image stops inserting into InnoDB when the table it reaches ~60GB.

In a nutshell, InnoDB is not designed for that kind of operations. Solutions? Splitting the table is usually the number one call- specially because partitions tend not to be access with equal frequency. Compression could help with lower storage requirement, but it may change the bottleneck to the CPU on inserting, and it may make things worse regarding caching.

Probably the best option would be to go for a different engine, which works well for lots of rows that is disk-bound. TokuDB is one of the engines that has been heard a lot lately that tries to cover that market gap, and has some interesting benchmarks.