Hi David, Ursula, Pierre I really appreciate your valuable input in this regard.
I did some search on Google/Drupal about indexing. Here are my understanding. Please correct me if wrong.
1. Indexing are done per coulmn basis in a table. 2. It helps during search operation to locate a particular row having same coulmn value as that of search string.
I am yet to find, how to acheive it. I assume, I need to modify hook_schema API in my module install file. Assuming I want to index the 'name' coulmn, probably I need to add below line in hook_schema (towards end of API).
'indexes' => array('name' => array('name'),),
Please correct me if my assumption is wrong. *The next BIG question - * I have two fields namely expert skill set and average skill set , those contain expert level and average level skills of an individual. My seacrh logic will be based on skill set.
Lets take an example. Say in row 10, expert skill set = PHP, Web2.0, Android, IPhone, HTML Say in row 10, average skill set = Database, TCL/TK, Pearl, C, C++, Java
(Please note that skill sets are comma separated).
When a potential recruiter searches resumes for Android , then I want all the candidates resumes with Android skill set should be shown in a view in tabular format, each page showing say 25 rows.
Now in this case, coulmn value is "PHP, Web2.0, Android, IPhone, HTML". And search string is "Android".
So will search query will retreive the row 10? My concern is coulmn value and search string are not a complete match, but a partial match.
Best Regards Austin
On Thu, Mar 17, 2011 at 11:09 PM, Ursula Pieper dramamezzo@gmail.comwrote:
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@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@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
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]