Alex Rivera | Logout

SQL: what exactly do Primary Keys and Indexes do?

Asked 2009-08-22T08:19:49.990
21

I've recently started developing my first serious application which uses a SQL database, and I'm using phpMyAdmin to set up the tables. There are a couple optional "features" I can give various columns, and I'm not entirely sure what they do:

  • Primary Key
  • Index

I know what a PK is for and how to use it, but I guess my question with regards to that is why does one need one - how is it different from merely setting a column to "Unique", other than the fact that you can only have one PK? Is it just to let the programmer know that this value uniquely identifies the record? Or does it have some special properties too?

I have no idea what "Index" does - in fact, the only times I've ever seen it in use are (1) that my primary keys seem to be indexed, and (2) I heard that indexing is somehow related to performance; that you want indexed columns, but not too many. How does one decide which columns to index, and what exactly does it do?

edit: should one index colums one is likely to want to ORDER BY?

Thanks a lot,

Mala

sql phpmyadmin
Edit
Report

2 Answers

7

The primary key is basically a unique, indexed column that acts as the "official" ID of rows in that table. Most importantly, it is generally used for foreign key relationships, i.e. if another table refers to a row in the first, it will contain a copy of that row's primary key.

Note that it's possible to have a composite primary key, i.e. one that consists of more than one column.

Indexes improve lookup times. They're usually tree-based, so that looking up a certain row via an index takes O(log(n)) time rather than scanning through the full table.

Generally, any column in a large table that is frequently used in WHERE, ORDER BY or (especially) JOIN clauses should have an index. Since the index needs to be updated for evey INSERT, UPDATE or DELETE, it slows down those operations. If you have few writes and lots of reads, then index to your hear's content. If you have both lots of writes and lots of queries that would require indexes on many columns, then you have a big problem.

answered 2009-08-22T08:53:28.550
6

The difference between a primary key and a unique key is best explained through an example.

We have a table of users:

USER_ID number 
NAME varchar(30)
EMAIL varchar(50)

In that table the USER_ID is the primary key. The NAME is not unique - there are a lot of John Smiths and Muhammed Khans in the world. The EMAIL is necessarily unique, otherwise the worldwide email system wouldn't work. So we put a unique constraint on EMAIL.

Why then do we need a separate primary key? Three reasons:

  1. the numeric key is more efficient when used in foreign key relationships as it takes less space
  2. the email can change (for example swapping provider) but the user is still the same; rippling a change of a primary key value throughout a schema is always a nightmare
  3. it is always a bad idea to use sensitive or private information as a foreign key
answered 2009-08-22T08:53:48.157

Your Answer