Hacker News Reader

Comment by NhanH | Hacker News Reader

Well, this is heresy. Does that mean we are now officially boycotting Uber?

Joke asides, one thing I've been trying to figure out for awhile is the limitation at which certain components/ systems broke down. Basically, something along the line of "given X records, this operations would take Y time, or would cause Z A B C problems". I've actually got developers friends asking me how fast a simple "SELECT * FROM X WHERE index=?" would take on a million row table, since they were surprised that some NoSQL DB could do a query on hundred million rows in a few seconds.

I guess that's part of why you only learned how to scale after having done it once.

View on HN

"surprised that some NoSQL DB could do a query on hundred million rows in a few seconds"

The tone seems to suggest this is fast, a simple index query in an RDBMS of even a hundred million rows will take milliseconds on even a weak computer.

Justify that with actual math, please? I don't think you know how computers (processors, bus, memory access, etc) work. How exactly do you think an RDBMS can query an index at a hundred billion rows per second?

Illniyar is right, a b-tree with a branching factor of 100 can index 100M rows in 4 levels. Even with the index on a spinning disk, should have no trouble coming in well under 100ms.

Indexes like this are also typically in memory to begin with, making it even faster, since you only have to find the actual record on disk.

Even better, if you only need one field from the record, and it's part of a compound index, you can frequently return the data from just the indexes; no disk seeks required. Small tip with InnoDB on MySQL - any non-primary key index is automatically a compound index with the primary key.

The parent said a query of a hundred million rows, i.e.: `select * from users where id = ?` when there are a hundred million users. That's obviously not scanning a hundred million rows, since the index will be a b-tree which by the power of logarithms is not so many levels deep.

Like Illniyar, that's also what I interpreted GP to mean when they said:

> "surprised that some NoSQL DB could do a query on hundred million rows in a few seconds"

which is not fast at all. However, if it actually did something on a hundred million rows that would be impressive. Not sure exactly sure which they meant, though.

I was testing some JSONB indexing and did a date-range query on a json doc for a table with 100m records to test with, it returned top 10 records that met the criteria in ~4s, figured out how to index the json doc for dates and this dropped to ~25ms.

This was running on an old Core i5 / 16gb of ram with a lot of junk running on Windows.

It would probably have been faster on a production environment.

Reply on news.ycombinator.com