search on custom field add in custom form in drupal

Question

we have made a custom form for registration in drupal. where we have add firstname,lastname,city,skills. Now we want to search user on these following criteria. But if when drupal made a new field, in backend it make a table with a field name. Eg:field_data_field_account_type

like we have writtern code for search on te basis of account type

   function getUsersList($type=null){
$query = db_select('users', 'u');
$query->join('field_data_field_account_type', 'at', 'u.uid = at.entity_id'); 
$query->fields('at')
->fields('u')
->condition('at.field_account_type_value',$type,'=')
->orderBy('created', 'DESC');

$result = $query->execute();
$users = array();
while($record = $result->fetchAssoc()) 
{
    $user_fields = user_load($record['uid']);
    $users[] = $user_fields;
}
return $users;
   }


but if we will search on multiple tables using joins search will become slow.

So, what approach we should follow to get name by firstname,city,skills, without using joins.

Is there any form fields api function in drupal to solve it.

I found one solution but here i m facing one problem .Here is my code

      i found some solution for my problem but i am facing one problem              

    $query = new EntityFieldQuery();
     $results=$query->entityCondition('entity_type', 'user');
     if($type!=null)
     {
      $query->fieldCondition('field_account_type', 'value',$type, '=');
    }
           if($name!=null)
           {

 $query->fieldCondition('field_first_name', 'value',"%$name%", 'like');

//$query->fieldCondition('field_last_name', 'value',"%$name%", 'like');
           }

           /*if($city!=null&&isset($city))
           {
           $query->fieldCondition('field_city', 'value',"%$city%", 'like');
           }
            if($skill!=null&&isset($skill))
           {
    $query->fieldCondition('field_skills', 'value',"%$skill%", 'like');
            }*/
           $results->execute();


This code only work on first name , bu not for last name.lIKE here and condition is working for last name 

so can i design query like this using or in which only one textbox .seach user by firstname,lastname,city,skil etc.

Answer 1

i doesn't get any solution so i copied the data of users in another table so tat searching become fast.

my code is :

function users_user_insert(&$edit, $account, $category) {
$coFounder      =   '';
 $founder       =   '';
 if($account->field_account_type['und'][0]['value']=='co-founder'){
$coFounder      =   $account->field_account_type['und'][0]['value'];
 }
 else if(($account->field_account_type['und'][0]['value']=='founder')&&($account-    
   >field_account_type['und'][1]['value']=='co-founder'))
  {
$founder        =   $account->field_account_type['und'][0]['value'];
$coFounder      =   $account->field_account_type['und'][1]['value'];
 }
 else{
$founder        =   $account->field_account_type['und'][0]['value'];
  }


   $result = db_insert('lew_users_meta')->fields(array('uid'=>$account-    
   >uid,'name'=>$account->name,'first_name'=>$account->field_first_name['und'][0]
   ['value'],
'last_name'=>$account->field_last_name['und'][0]['value'],'mail'=>$account->mail,
'city'=>$account->field_city['und'][0]['value'],
     'skills'=>$account->field_skills['und'][0]['value'],


          'coFounder'=>$coFounder,'founder'=>$founder,'created'=>time()
                        )


                       )                 
                       ->execute();

         }

     function users_user_update(&$edit, $account, $category) {
     $coFounder     =   '';
     $founder       =   '';
    if($account->field_account_type['und'][0]['value']=='co-founder'){
$coFounder      =   $account->field_account_type['und'][0]['value'];
     }
 else if(($account->field_account_type['und'][0]['value']=='founder')&&($account-   
  >field_account_type['und'][1]['value']=='co-founder'))
 {
$founder        =   $account->field_account_type['und'][0]['value'];
$coFounder      =   $account->field_account_type['und'][1]['value'];
 }
 else{
$founder        =   $account->field_account_type['und'][0]['value'];
  }

    $result = db_update(' lew_users_meta')
                       ->fields(array('name'=>$account->name,'first_name'=>$account-    
      >field_first_name['und']['0']['value'],
'last_name'=>$account-     
    >field_last_name['und'][0]['value'],'mail'=>$account->mail,
'city'=>$account->field_city['und'][0]['value'],
    'skills'=>$account->field_skills['und']['0']['value'],

      'founder'=>$founder,'coFounder'=>$coFounder,'modified'=>time()
                        )


                       )
                       ->condition('uid', $account->uid,'=')
                       ->execute();



  }

so we have used hook to insert data in another table.so that we should not joins the
table.

if you have better solution please post here

Answer 2

The first thing I have to say is I know nothing about drupal, but I would suggest a different point of view. your problem is that joining is really slow, so you'd like a non-join solution.

What I think instead is to improve the join speed instead of not using it, because I think "join" is a fairly reasonable thing to do under your situation.

to spped things up, I'd suggest adding index for uid in users table. Also entity_id & field_account_type_value in field_data_field_account_type.

having these 3 column indexed should massively increase your query speed if you haven't index it before.