Primary keys mean that there is one and only one value for each record. In a resume table that you posted earlier that means a person can have one and only one resume with name. I would typically reverse this. The uid of a user doesn't change, but it's name does, so you don't want to be using name as a primary key. Use uid as primary key and only put an index on name if you're talking about commonly looking up by or sorting by name. Primary keys usually imply an index behind it but also add requiring one and only one value for each record. You need the index to find out if a record already exists quickly. A primary key typically can be thought of as an index with a uniqueness constraint.
Indexes generally dramatically improve lookup time in large volume databases at the expense of taking a little longer to write records.
I would strongly recommend that you pick up a book on database design to learn these concepts.
________________________________
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Austin Einter Sent: Thursday, March 17, 2011 4:26 AM To: development@drupal.org Cc: support@drupal.org Subject: [support] Doubt on Index and Primary key
HI All
In .install file, in hook_schema unction, we are mentioning index field and primary key field.
I am wondering, how to choose which one should be index and which one should be primary key.
Does that affect the performance (in search, add, delete of records).
What I am thinking at this point of time, my database will contain job seekers name, mail-id, contact number, skill set (as comma separated values) and resume.
For index purpose, I am thinking I will use '$user->uid' as Index and and 'name' as primary key.
I am bit confused how it it is going to affect search/add/delete time (assuming huge number of records in database) and also database management.
Please guide me.
Regards
Austin