[support] Doubt on Index and Primary key

[Metzler, David]

Pierre is spot on here. 

That is why most dbas would advise against storing this data in a comma separated list in a single field.  An index cannot really be used to search within the text cause you are forcing to examine every row anyway.  I can't programtically say lets start with the N's, now is there a nokia in there (that's an oversimplification intentionally to make a point). Rather I would make a single skill table that housed the values. If UID is the primary key for the resume, then you'd make a table with 

On a separate note, you do understand that the site that you're talking about building could be done without you writing ANY code? Basically the site you've described can be implemented with content_profile, cck and views modules, allowing you to build custom content types that are tied (one per user).  You could then use taxonomys for skill sets an all this would be written for you?

Dave
-----Original Message-----
From: support-bounces at drupal.org [mailto:support-bounces at drupal.org] On Behalf Of Pierre Rineau
Sent: Friday, March 18, 2011 6:45 AM
To: support at drupal.org
Subject: Re: [support] Doubt on Index and Primary key

Le vendredi 18 mars 2011 à 07:55 +0530, Austin Einter a écrit :
> 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. 

And it also help for JOIN statements and such, but yes it is.

> 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'),),

You are right. That's probably also what the documentation say :) If you
forgot an index you can add it later in a hook_update_N() implementation
so don't worry you don't have your indexes to be perfect on the first
try.

You can also do multi-column indexes, which can help (depending on the
DBMS you use) for queries that uses multiple WHERE.

You don't have to put indexes everywhere, just put some over the field
you will use for querying or joining.

> 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.

Here you should probably normalize your schema, which means store the
skills into their own table, and provide a n <-> n relation table
between your data table and the skill table. You can then query doing a
JOIN statements which will probably be more natural for developers,
faster, avoid string comparison (really slower than integers).

Doing schema normalization is not only good for performances, but it
will also avoid redundancy.

But, I won't teach you the full relational model theory, you should
start there maybe before doing SQL. Google would give you excellent
theorycal explaination documents that may provide you beginner and or
advanded theory and technical documentation.

Pierre. 


-- 
[ Drupal support list | http://lists.drupal.org/ ]