Indexes and Primary Keys

[Bardius]

Just begun work on how to integerate the mighty PHP with mySQL. There's a couple of things which I don't really understand and they are as the topic suggests: indexes and primary keys. Seeing as the people in here are no different than me(well I like to think so); I was wondering whether any one of you helpful lot could care and tell me in English terms what these two mean when it comes to database tables...

[freakysid]

Well a primary key is central to the relational theory and relational database design. Every row (also known as a record or tuple) in a table (also known as a relation) mut be uniquely identified by a primary key. For example, in a relational scheme the first normal form says that each row of a table must be uniquely identified by its primary key:

This is not allowed in a relational database. (two records the same in a table).

id   name   colour
------------------
1    foo    red
1    foo    red

further, if the primary key is defined as id then this is also not allowed

id   name   colour
------------------
1    foo    red
1    bar    blue

Because each record must have a unique primary key.

Sometimes we can make a primary key be a composite of two or more attributes of a table. For example if we defined a composite primary key for the above table of (name, colour) then the second example would not break the first normal form because foo,red is not the same as bar,blue. So in short, the primary key is the unique identifier of a row in a table.

Indexes are not mandatory in your database design and do not relate to the relational theory. Indexes are more a performance enhancing tool for your database. An index is just like an index in a book. If we want to quickly reference a specific item, we look in the index to see which pages that item is mentioned. Same thing with a database table. We can index certain attributes in our table so that we can look up records by that attribute quickly.

For example, in the above example, we might create an index on the attribute colour. This will actually create an index table in our database that will hold a pointer to each record in the table we have indexed along with its value for colour. This speeds up lookups where we are searching for records by colour. Say we want to find all the records with colour='red'. First, the database will lookup the index and see which records have a value of 'red' for colour. Then it will look up the main table, jumping directly to the relevent records and grabbing all the data for that record.

The down side to using indexes is while they speed up SELECTs they slow down INSERT, UPDATE and DELETE queries, because the record needs to be changed in the table and in the index/s.

As a rule of thumb, if you have a query that could do with some speeding up, then create an index on all of the attributes named in the WHERE clause in your query.

Eg,
SELECT * FROM TableName
WHERE foo=$foo
AND bar=$bar

Here you would want to create an index on (foo,bar)

There is a really good introductory article on indexes at www.phpbulder.com. However, before you worry about indexes, it is vital that you understand database design and data normalisation to the general third normal form (3NF). See Skunks links (look in my signature) for more resources. Cheers

[Bardius]

So in other words, a field that's set as the Primary Key is basically a unique field? The only difference (at least to my eye) between the two is that you can have more than unique field and only one sole field set as the primary key?

[DR_LaRRY_PEpPeR]

Originally posted by Bardius
So in other words, a field that's set as the Primary Key is basically a unique field? The only difference (at least to my eye) between the two is that you can have more than unique field and only one sole field set as the primary key?

yeah, you got it.