CODEWITHSUNDEEP
sqlprimary-key
krafty
krafty

Reputation: 435

Difference between primary key search and fields search SQL

Is there any difference with performance when we are searching / selecting the records with the primary key and the other fields of same sizes?

Also the comparison in performances of such queries with GUID as primary keys and Int as primary keys will be different?

Upvotes: 4

Views: 6049

Answers (4)

Jerzy Pawlikowski
Jerzy Pawlikowski

Reputation: 1969

Difference is that primary key has index so searching by primary key is usually faster than by other field that hasn't index. Anyway you can have index on field which is not primary key then there is no difference in searching.

Upvotes: 1

nvogel
nvogel

Reputation: 25534

A key is a logical feature of the database whereas performance is determined entirely by physical features: storage format, indexing and the methods used internally for data access. In principle therefore the answer is No. There is no fundamental reason why there needs to be a difference between accessing data by key or by any other non-key attribute(s). A more specific answer might be possible only if we knew more information about storage, indexing and what DBMS you are using.

Upvotes: 2

vijikumar
vijikumar

Reputation: 1815

Yes. There is a performance issue.

Search with primary key is much more faster than search with other fields. Because primary key is unique + notnull and also its having index.

Upvotes: 0

Vikram Jain
Vikram Jain

Reputation: 5588

Unique Keys: The columns in which no two rows are similar

Primary Key: Collection of minimum number of columns which can uniquely identify every row in a table (i.e. no two rows are similar in all the columns constituting primary key). There can be more than one primary key in a table. If there exists a unique-key then it is primary key (not "the" primary key) in the table. If there does not exist a unique key then more than one column values will be required to identify a row like (first_name, last_name, father_name, mother_name) can in some tables constitute primary key.

Index: used to optimize the queries. If you are going to search or sort the results on basis of some column many times (eg. mostly people are going to search the students by name and not by their roll no.) then it can be optimized if the column values are all "indexed" for example with a binary tree algorithm.

Upvotes: 2

Related Questions