php - Mysql Match Against & Like search

Question

SELECT * FROM `db` 
WHERE MATCH (city) AGAINST ('south ban' IN BOOLEAN MODE)

SELECT * FROM 'db'
WHERE city LIKE '%south ban%'

I have 2 queries, one is use Match against, the other is LIKE,

When I try to search 'south bank'

Match wont return if user type south ban but Like will return the result

How can I improve Match against search?

I did testing,

(country LIKE '%south%' || state  LIKE '%south%' || city  LIKE '%south%') &&
(country LIKE '%bank%' || state  LIKE '%bank%' || city  LIKE '%bank%')

MATCH (country, stateprov, city) AGAINST ('south bank*' IN BOOLEAN MODE)

for 3 millions rows

Match - 69,310 rows - 2.5 sec

LIKE - 67,092 rows - 1.87 sec

How come Like is faster than match?

Answer 1

If you use a IN BOOLEAN MODE query without +, match is actually rating each row, which takes time.

Check out this info from http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html:

(no operator)

By default (when neither + nor - is specified) the word is optional, but the rows that contain it are rated higher. This mimics the behavior of MATCH() ... AGAINST() without the IN BOOLEAN MODE modifier.