Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc?

Question

If the database data will be on NVME SSD which let's assume is 4x faster than an ordinary SSD will queries like SELECT something OFFSET 100000 and the like typically also be much faster (of course when they are not cached/buffered etc)?

Bob, Did you install NVME? Are your database tables ALL using innodb engine? What is your result of SELECT @@innodb_io_capacity; ? Also, what is result of SELECT @@version; ? — Wilson Hauck, Aug 03 '19 at 13:04
@WilsonHauck I ended up with a conclusion, that NVME won't give a meaningful performance boost to my apps, as the slowest things happen in memory anyway (which is much faster than disk), as RDMBS loads the indexes into memory given the server has enough of it (and it does in my case). — Bob, Aug 06 '19 at 10:18
If you could share answers to the 4 questions it would be meaningful to the community. — Wilson Hauck, Aug 06 '19 at 15:17

score 3 · Accepted Answer · answered May 18 '18 at 23:25

Since I/O is the dominant factor in how long it takes an SQL query to run, then it should help.

Some pros and cons, all mixed up:

Well designed schema and well written queries avoid I/O. OFFSET 100000 should be avoided, regardless of the I/O.
UUIDs are notoriously random.
The I/O speed has very little effect when everything that you need is cached.
It won't be 4x. By decreasing the I/O bottleneck, CPU time becomes more prevalent.

Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc?

1 Answers1