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.