Simple explanation of indexing... primary, unique and index keys PLEASE!!!

[knobby2k]

 

Can anyone please help...

 

I want to understanding indexing and best practise for indexing. I need to get my head around how / when to use primary, unique or index keys. Please don't think I have not read up anywhere as I have read countless websites and watched numerous web videos on youtube but i still don't think i've got my head around it so if anyone can help that would be great.

 

Could someone explain it and then put it into a real life explanation of how to use it on a user table for example consisting of names and addresses as below...

 

 

idfirstnamesurnamehouse_numberaddress_line_1address_line_2address_line_3postcode

1bobfisher10morton streetmayflowerhullhu11sd

2jillsmith6john streethawthorn closeleedsls221sd

3karenholmes2wilson streetdownsfordbradfordbd1 2rh

 

...anyway, i hope someone can help me.

 

much appreciated

 

[mikosiko]

very simple real life explanation (non trying to insult your intelligence of course):

 

think in a library with hundred of books... (your table... each book is a row)

 

- In a library a book (and everything contained on it) is identified unequivocally by it ISBN number (Primary KEY... id in your example table)

 

- Now... open the book... what do you see in the first couple pages?.... right!!... the Index pages... how do you use that Index pages?.... to locate in the faster way a chapter/topic/etc. .... (INDEX) (in a table could be composed by one field or multiples fields)... in your example, postcode is a candidate to be an INDEX.

 

- In the book a Chapter# should be unique. A UNIQUE Index is just an specialized Index that allows you to define which field or combination of fields can not be duplicated in your table... p.e: if in your example table you add a column to identify unequivocally a user (SS #, Employee #, etc) that should be a UNIQUE INDEX.

 

hope this simplistic explanation help

[knobby2k]

Thanks... an explanation like that was perfect! you don't know how many things i've read that have just made it more confusing than it needed to be.

 

So things that would help narrow down a search should be indexed, for example there could be 12 people with the surname smith, 10 with jones, etc... therefore indexing surname would help it quickly narrow down to smith by kind of grouping common fields if i used a WHERE clause in my SQL statement to search?

 

is it advisable to have more than one index? using the example above... making both the surname and postcode an index?

 

Thanks for your help

[mikosiko]

 

So things that would help narrow down a search should be indexed...

 

is it advisable to have more than one index? ...

 

that is were things are a little more interesting and complicated... indexes are no "free".. correctly used could provide you the advantage of faster search, but slower write operations.. there are a lot of considerations that need to be done before decide to use indexes or not, like: table volume (#rows), index cardinality (how selective is an index), more used queries and the impact on performance on them with or without indexes, read vs write ratio, more used search criteria in the WHERE clause, etc... just to name a few.

 

if you are working with small tables, start only with your primary key.. you can add more indexes later if necessary... I will not recommend to add indexes on names due to the probably high cardinality  (lower the better).

 

Profiling and EXPLAIN plans are tools that could help you to evaluate your queries and analyze indexes addition/removal

 

[knobby2k]

thanks, makes sense to me now! i'll more this as solved!

 

Cheers