[support] Doubt on Index and Primary key

[Metzler, David]

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 at drupal.org [mailto:support-bounces at drupal.org] On
Behalf Of Austin Einter
Sent: Thursday, March 17, 2011 4:26 AM
To: development at drupal.org
Cc: support at 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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/support/attachments/20110317/570f0e7b/attachment-0001.html