php - Search code optimisation - CakePHP -

i have database containing list of files (around 6000 files). these files have additional details linked them (such project numbers, sectors, clients, comments, disciplines).

although code , search works, it's slow. simple search 2 terms takes minute complete.

my code below. want know is, can simplify , optimize search function?

public function search() {     $terms = explode(' ',$this->request->data['kmfiles']['search']);     $possible = 0;     $matches = array();     foreach($terms $term) {         $files = $this->kmfile->find('list',             array(                 'conditions' => array(                     'file_name like' => '%' . $term . '%'                 ),                 'fields' => array('id')             )         );         $possible++;         $clients = $this->kmclient->find('list',             array(                 'conditions' => array(                     'clients like' => '%' . $term . '%'                 ),                 'fields' => array('km_file_id')             )         );         $possible++;         $disciplines = $this->kmdiscipline->find('list',             array(                 'conditions' => array(                     'disciplines like' => '%' . $term . '%'                 ),                 'fields' => array('km_file_id')             )         );         $possible++;         $projects = $this->kmproject->find('list',             array(                 'conditions' => array(                     'projects like' => '%' . $term . '%'                 ),                 'fields' => array('km_file_id')             )         );         $possible++;         $sectors = $this->kmsector->find('list',             array(                 'conditions' => array(                     'sectors like' => '%' . $term . '%'                 ),                 'fields' => array('km_file_id')             )         );         $possible++;         $comments = $this->kmcomment->find('list',             array(                 'conditions' => array(                     'comments like' => '%' . $term . '%'                 ),                 'fields' => array('km_file_id')             )         );         $possible++;         $matches = array_merge($matches,$files,$clients,$disciplines,$projects,$sectors,$comments);     }     if(count($matches) > 0) {         $numberofmatches = array_count_values($matches);         $matches = array_unique($matches);         $k=0;         foreach($matches $match) {             $result = $this->kmfile->find('all',                 array(                     'conditions' => array(                         'id' => $match                     )                 )             );         $results[$k] = $result[0];         $results[$k]['relevance'] = round(($numberofmatches[$match] / $possible) * 100,2);         $relevance[] = $results[$k]['relevance'];         $k++;     }         array_multisort($relevance,sort_desc,$results);         $stats['count'] = count($results);         $stats['terms'] = $this->request->data['kmfiles']['search'];         $this->set(compact('results','stats'));     } else {         $stats['count'] = 0;         $stats['terms'] = $this->request->data['kmfiles']['search'];         $this->set(compact('stats'));     } } 

i know it's long piece of code, i'm new cakephp have no idea improve it.

any assistance appreciated.

to make faster, have defer responsibility database possible (databases fast these days!) , minimise , forth between php , database. ideally, you'd fetch search results in single query (ie, single find call).

you'd specify joins, such kmfile model left-joined kmclient, kmproject, etc tables.

then, it's matter of building long conditions array. in cake, can specify 'or' conditions this:

array('or' => array(     array('post.title like' => '%one%'),     array('post.title like' => '%two%') )) 

check out doco on complex find conditions. conditions array like:

array('or' => array(     array('kmfile.file_name like' => '%term1%'),     array('kmfile.file_name like' => '%term2%'),     array('kmdiscipline.disciplines like' => '%term1%'),     array('kmdiscipline.disciplines like' => '%term2%'),     array('kmproject.projects like' => '%term1%'),     array('kmproject.projects like' => '%term2%'),     // , on... )) 

obviously you'd want use loop build conditions array.

then single find, on kmfile model, joined related models, big list of conditions. return list of matches, , shouldn't take long.

it's possible calculate kind of relevancy score in same single query, though don't know how. , in case, once find results in single query, shouldn't take long loop through them in php code , calculate relevancy each one.