Search Optimization


I had a client point something out I never really thought about.

A product called “SALA Ultra-Lok” is in the database. If you search “sala ultra-lok”, or “sala”, or “ultra-lok”, etc, no problems it is found.
But if you search “sala ultra lok” (or any variation without the hyphen), it will not be found.

I’ve fixed it with this:

$strings = explode(" ", $search);
			foreach($strings as $key => $value){
				$q .= " 
					AND (
						p.productTitle LIKE '%".$this->escape_string($value)."%' OR
						p.productSKU LIKE '%".$this->escape_string($value)."%' OR 
						b.brandName LIKE '%".$this->escape_string($value)."%'

Is this right? Or is there a better way?



this function has no meaning without code, so the question cant be answered.



Idea being: Anything that isnt a alphanum = %.

That looks fine.

But it will return many results if people search for something like a-team as “a team” because it returns everything with a in it.

A quick fix would be to use only search terms greater than 2 chars and discard the rest. So for a-team just look for team.

That’s just a part of the module. It just calls the mysql_real_escape_string on the string. The question is more about the search string and breaking it up to make the search more broad.


Edited my post above. Gotta stop responding too quickly :stuck_out_tongue_winking_eye:

and yes, filtering the size of the terms is a good idea too.

Thanks, good idea. Strangely, “sala ultra lok” will return just those 8 products, where a “sala” as a term returns the 192 products containing sala.

That looks more efficient. I’ll give it a whirl. Cheers.

Because your search string doesnt look for those terms independantly.

% is the wildcard character. Putting in “sala ultra lok” returns the query string: p.productTitle LIKE ‘%sala ultra lok%’ OR …
Which will only return products using that phrase. If you want to tokenize the result, you’re gonna have to reconstruct the code a bit; change the preg_replace to a preg_split, and construct a multiple-or query using each part.

Give example, SL. Okay, here goes.

DB Entries:

  1. sala ultra lok toolkit
  2. sala ultra-lok toolkit
  3. ultra-lok sala toolkit
  4. ultra mega wrench

Search Term: sala ultra lok

Your original query would match 1.
The preg_replace would match 1 and 2.
Preg-splitting and compound-or would match all 4.
Preg-splitting and compound-AND would match 1,2, and 3.

OK thanks. So it looks like my best option would be Preg-splitting and compound-AND. Much appreicated.

As an alternative, because you are searching 3 fields in a single table, you could look at creating a [google]mysql FULLTEXT index[/google] on those fields, this may well do a better job of finding these “close” matches.

I’d copy that table, set up the index and see if it returns matches that are in line with your clients expectations.