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.
- Indexing are done per coulmn basis in a table.
- 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.