I am coding a search routine on a products db that gives a site visitor the ability to search on model,title,description,and price. (not all at once).
$query='SELECT * FROM '.TABLE_PRODUCTS.' ORDER BY id;';
if($result=db_query($query,$dblink)){
while($row=db_fetch_array($result,MYSQL_ASSOC)){
switch ($method){
case 'MODEL':
match_model($match,decodedb($row['id']),$search_string);
break;
case 'TITLE':
match_title($prods,array('title'=>decodedb($row['title']),'id'=>decodedb($row['id'])),$search_string);
break;
case 'DESCRIPTION':
match_description($prods,array('desc'=>decodedb($row['description']),'id'=>decodedb($row['id'])),$search_string);
break;
case 'PRICE':
match_price($prods,array('price'=>decodedb($row['price']),'id'=>decodedb($row['price'])),$search_string);
break;
}
}
}
I know my switch shouldn't be in the while loop but....
Anyway, i am using custom functions to match words in the model, title and description. a combination of metaphone() and levenshtein() to determine the % of match. the formula is different in each case.
The problem is this is a costly operation to loop through every product every time a search is done. (although they may only be adding 500 or so at most).
Is there any way to speed up the search by incorporating some sort of index/hash table in the db using metaphone or levenshtein values? any ideas?
