<div>Hi David, Ursula, Pierre<br>I really appreciate your valuable input in this regard.</div>
<div> </div>
<div>I did some search on Google/Drupal about indexing. Here are my understanding. Please correct me if wrong.</div>
<div> </div>
<div>1. Indexing are done per coulmn basis in a table.</div>
<div>2. It helps during search operation to locate a particular row having same coulmn value as that of search string. </div>
<div> </div>
<div>I am yet to find, how to acheive it. I assume, I need to modify hook_schema API in my module install file.</div>
<div>Assuming I want to index the 'name' coulmn, probably I need to add below line in hook_schema (towards end of API).</div>
<div> </div>
<div>'indexes' => array('name' => array('name'),),</div>
<div> </div>
<div>Please correct me if my assumption is wrong.<br></div>
<div><strong>The next BIG question - </strong></div>
<div>I have two fields namely <font color="#3333ff">expert skill set</font> and <font color="#3333ff">average skill set </font>, those contain expert level and average level skills of an individual.</div>
<div>My seacrh logic will be based on skill set.</div>
<div> </div>
<div>Lets take an example.</div>
<div>Say in row 10, expert skill set = PHP, Web2.0, Android, IPhone, HTML</div>
<div>Say in row 10, average skill set = Database, TCL/TK, Pearl, C, C++, Java</div>
<div> </div>
<div>(Please note that skill sets are comma separated).</div>
<div> </div>
<div>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.</div>
<div> </div>
<div>Now in this case, coulmn value is "PHP, Web2.0, Android, IPhone, HTML". And search string is "Android".</div>
<div> </div>
<div>So will search query will retreive the row 10?</div>
<div>My concern is coulmn value and search string are not a complete match, but a partial match.</div>
<div> </div>
<div>Best Regards</div>
<div>Austin</div>
<div> </div>
<div> </div>
<div> </div>
<div> </div>
<div class="gmail_quote">On Thu, Mar 17, 2011 at 11:09 PM, Ursula Pieper <span dir="ltr"><<a href="mailto:dramamezzo@gmail.com">dramamezzo@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex" class="gmail_quote">Just to add to what David and Pierre wrote:<br><br>Especially for a large table, proper indexing is very important. In<br>
practical terms, you want to have an index on the fields after the<br>"where" keyword in MySQL. For example: If the query would be "where<br>name='whatever'", there should be an index on "name"; however, if a<br>
frequent query would be "where name='whatever' and city='New York',<br>then it might be better for the index to comprise both. (create index<br>indexname on jobs (name,city);), and the single column index would not<br>
be necessary anymore.<br><br>You can see the indices of your table using "show index from jobs;".<br>As David pointed out, the primary key is indexed automatically. And<br>yes, get a good book.<br><br>Ursula<br>
<div>
<div></div>
<div class="h5"><br>On Thu, Mar 17, 2011 at 8:30 AM, Metzler, David <<a href="mailto:metzlerd@evergreen.edu">metzlerd@evergreen.edu</a>> wrote:<br>> Primary keys mean that there is one and only one value for each record. In<br>
> a resume table that you posted earlier that means a person can have one and<br>> only one resume with name. I would typically reverse this. The uid of a<br>> user doesn’t change, but it’s name does, so you don’t want to be using name<br>
> as a primary key. Use uid as primary key and only put an index on name if<br>> you’re talking about commonly looking up by or sorting by name. Primary keys<br>> usually imply an index behind it but also add requiring one and only one<br>
> value for each record. You need the index to find out if a record already<br>> exists quickly. A primary key typically can be thought of as an index with a<br>> uniqueness constraint.<br>><br>><br>><br>
> Indexes generally dramatically improve lookup time in large volume databases<br>> at the expense of taking a little longer to write records.<br>><br>><br>><br>> I would strongly recommend that you pick up a book on database design to<br>
> learn these concepts.<br>><br>><br>><br>> ________________________________<br>><br>> From: <a href="mailto:support-bounces@drupal.org">support-bounces@drupal.org</a> [mailto:<a href="mailto:support-bounces@drupal.org">support-bounces@drupal.org</a>] On<br>
> Behalf Of Austin Einter<br>> Sent: Thursday, March 17, 2011 4:26 AM<br>> To: <a href="mailto:development@drupal.org">development@drupal.org</a><br>> Cc: <a href="mailto:support@drupal.org">support@drupal.org</a><br>
> Subject: [support] Doubt on Index and Primary key<br>><br>><br>><br>> HI All<br>><br>> In .install file, in hook_schema unction, we are mentioning index field and<br>> primary key field.<br>><br>
><br>><br>> I am wondering, how to choose which one should be index and which one should<br>> be primary key.<br>><br>> Does that affect the performance (in search, add, delete of records).<br>><br>><br>
><br>> What I am thinking at this point of time, my database will contain job<br>> seekers name, mail-id, contact number, skill set (as comma separated values)<br>> and resume.<br>><br>><br>><br>> For index purpose, I am thinking I will use '$user->uid' as Index and and<br>
> 'name' as primary key.<br>><br>><br>><br>> I am bit confused how it it is going to affect search/add/delete time<br>> (assuming huge number of records in database) and also database management.<br>
><br>><br>><br>> Please guide me.<br>><br>><br>><br>> Regards<br>><br>> Austin<br>><br></div></div>> --<br>> [ Drupal support list | <a href="http://lists.drupal.org/" target="_blank">http://lists.drupal.org/</a> ]<br>
><br><font color="#888888">--<br>[ Drupal support list | <a href="http://lists.drupal.org/" target="_blank">http://lists.drupal.org/</a> ]<br></font></blockquote></div><br>