[support] Doubt on Index and Primary key

[Ursula Pieper]

Just to add to what David and Pierre wrote:

Especially for a large table, proper indexing is very important. In
practical terms, you want to have an index on the fields after the
"where" keyword in MySQL. For example: If the query would be "where
name='whatever'", there should be an index on "name"; however, if a
frequent query would be "where name='whatever' and city='New York',
then it might be better for the index to comprise both. (create index
indexname on jobs (name,city);), and the single column index would not
be necessary anymore.

You can see the indices of your table using "show index from jobs;".
As David pointed out, the primary key is indexed automatically. And
yes, get a good book.

Ursula

On Thu, Mar 17, 2011 at 8:30 AM, Metzler, David <metzlerd at evergreen.edu> wrote:
> 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
>
> --
> [ Drupal support list | http://lists.drupal.org/ ]
>