[support] Doubt on Index and Primary key

[Pierre Rineau]

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.