index files in database

Can somebody explain how index files work in a database?

Namely, a primary sec开发者_高级运维ondary and clustered index? What I don't understand is that primary indexes are use on ordered columns in the database and secondary ones are used for unordered files in database. I can understand by ordering a index file we can perform binary serach to find a record faster, but i guess what I dont get is what the point of using a primary index when it addresses an already ordered column? Can you not apply the optimised search algorithm on the column itself as opposed to creating a primary index?

I assume from you reference to Clustered indexes you are looking at SQL Server? To the best of my knowledge this is specific to the various flavors of SQL Server (e.g. Microsoft and Sybase). A clustered index in effect cuauses a table to be an ordered. The data in the table is inserted in the order of the clustered index. Other RDBMSes (e.g. Oracle, Informix, etc) do not use clustered indexes.

A primary index is a unique index. It is the one that indexes the primary key and enforces uniqueness. There can only be one primary key (thus one primary index) per table but there can be more that one unique column or combination of columns so we have candidate indexes; other unique indexes which are not the primary key.

A secondary index is not a term I have encountered but I believe (from a quick Google) that it is any other, non-primary index. As such it can be unique or not. If unique it is a candidate index.

So we end up with

1. clustered index
2. candidate index
3. primary index
4. secondary index

And an index can be more that one of these, e.g. secondary and unique and candidate.

The index speeds data access (in some cases) by allowing the RDBMS to go more quickly to the data in question. Imagine that I say you collect me a knife and fork. You might guess it is in the kitchen and go their and start looking through draws. If you had an index that said knives are kept in the 2nd draw in the kitchen you could go straight to the draw and get the knife.

Similarly because we write 'select * from table where col = 567' the RDBMS knows to go to the table to find the data. But if the col is indexed the RDMBS can go straight to the location of '567' and select it without having to look at each value in col.