[development] Doubt on Index and Primary key

[Pierre Rineau]

Le jeudi 17 mars 2011 à 16:56 +0530, Austin Einter a écrit :
> 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

Primary key semantically means "Unique not null and indexed".

So, primary key is an indexed, not null, and unique field. But, primary
key can be composed by more than one field (therefore the unique
constraint goes to the fields tuple).

Integer performances, for keys and indexes, are always way better than
varchars for querying and joining. Plus there are some existing known
bugs with MySQL and index on varchars, si I'd rather use integer
identifiers when I can. You can basically use index on other varchar
fields when you need it to boost specific queries.

Indexed integers for basic CRUD operations seems to be a wize choice, it
will probably be the most common operations done over your dataset.

Pierre.