How to optimize mysql full match

Question

Hey guys, do you know some trick about how can I optimize more a full match search?

The code that I'm trying to optimize:

$do = $this->select()
      ->where('MATCH(`name`,`ort`) AGAINST( ? IN BOOLEAN MODE)', $theString)
      ->order('premium DESC');

The search should search for some companies...and let's say that in the field name I have: Google and in the ort field I have new york.

I want to do a query like: google center in new york and to give me as a result all the google centers from new york only!

I hope you understand what I mean!

Answer 1

I think you should have a look at

• Full-Text Search Functions
• Using MySQL Full-text Searching

Answer 2

Your type of query might be matched better by a fulltext search in NATURAL LANGUAGE MODE instead of BOOLEAN MODE.

If you search for "google center in new york" in boolean mode you'll find any matches of all those keywords, but not necessarily in that order or matching those concepts.

Answer 3

use the relevance to sort

$do = $this->select("*, MATCH(`name`,`ort`) AGAINST ('{$theString}') AS score")
      ->where('MATCH(`name`,`ort`) AGAINST( ? IN BOOLEAN MODE)', $theString)
      ->order('premium DESC, score');