[support] Doubt on Index and Primary key

[Austin Einter]

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 at gmail.com>wrote:

> 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/ ]
> >
> --
> [ Drupal support list | http://lists.drupal.org/ ]
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/support/attachments/20110318/8ebb58c5/attachment.html