4

From my understanding, we can compare SQL vs NoSQL to array vs hashmap/dict.

(Let's consider PostgreSQL vs MongoDB just for a context)

SQL is arranged in tables and searches through the rows for what you're looking for.

NoSQL is arranged in a key-value way, so if you know the key, you'll get the value "directly" without needing to search through anything.

With the above considered, when I make a query in SQL using only the primary-key as my WHERE to get one item, does it still do a row search or does it do a "direct" hit on the row?

I hope my doubt has been understood

Mojimi
  • 2,561
  • 9
  • 52
  • 116

1 Answers1

6

Primary keys are guaranteed to be unique. Unique keys are implemented using indexes, which in all databases that I know of are b-trees.

A query on a primary key uses the b-tree to access the data. This is log(n) in complexity.

Some databases support other index structures, such as hash tables. That would generally make such a lookup more like O(1) rather than O(log n).

I don't think you are on a fruitful path trying to differentiate NOSQL from SQL databases by looking at such examples. You should look at the requirements they are trying to implement, starting with ACID properties and concepts such as delayed consistency.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I think what I'm trying to get at is, if I design my SQL database to accomodate only indexed queries, will it be as fast as a key-value NoSQL database? I want all the good features from SQL with the NoSQL speed. – Mojimi Jul 18 '19 at 18:39
  • @Mojimi . . . Generally, the speed will be good enough. But NOSQL relaxes ACID constraints, so it has more opportunity to be faster, particularly on parallel systems. It depends how important ACID is to your application. – Gordon Linoff Jul 18 '19 at 18:48