CODEWITHSUNDEEP
javasql
M.Dietz
M.Dietz

Reputation: 1000

Is there a difference between finding by primary key vs finding by unique column?

If i have an entity with a primary key id and an unique column name. Is there any difference whether i do a SQL request findById(long id) or findByName(String name). Can a search for the primary key be done in O(1) while the other one works in O(n)? Which data structures are used for saving them?

Upvotes: 0

Views: 877

Answers (3)

Filippo Possenti
Filippo Possenti

Reputation: 1410

If both columns were INTEGER, then the answer would be "no". A PRIMARY KEY is effectively a UNIQUE constraint on a column and little more. Additionally, as usually they both cause internal indexes to be created, again they behave basically the same way.

In your specific case, however, the NAME column is a string. Even though it has a UNIQUE constraint, by virtue of its data type you will incur in some performance loss.

As your question is probably dictated by "ease of use" to some extent (for your debugging purposes it's certainly easy to remember the "name" than it is to remember the "id") the questions you need to ask yourself are:

  • Will the NAME column always be unique or could it be change to something not unique? Should it actually be unique in the first place (maybe they set it up wrong)?
  • How many rows do you expect in your table? This is important to know as while a small table won't really show any performance issue, a high cardinality may start to show some.
  • How many transactions/second do you expect? If it's an internal application or a small amateurial project, you can live with the NAME column being queried whereas if you need extreme scalability you should stay away from it.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

From the perspective of order complexity of the operation, then the two are equivalent.

As others have pointed out, an integer lookup is generally faster than a string lookup. Here are three reasons:

  1. The index would typically be smaller because, integers are 4 bytes and strings are typically bigger.
  2. Indexes on fixed length keys have some additional efficiencies in the tree structure (no need to "find the end of the string").
  3. In many databases, strings incur additional overhead to handle collations.

That said, another factor is that the primary key is often clustered in many databases. This eliminates the final lookup of the row in data pages -- which might be a noticeable efficiency as well. Note that not all databases support clustered indexes, so this is not true in all cases.

Upvotes: 2

Richard Matriche
Richard Matriche

Reputation: 87

The difference is speed : Running SQL query against Integer will always be faster than running against a string.

Upvotes: 4

Related Questions