-1
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?

Ben
  • 2,562
  • 8
  • 37
  • 62
  • The faster performance of your queries using `LIKE` are probably due to MySQL returning previously cached results. – eggyal Sep 15 '13 at 00:27

1 Answers1

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.
pfrank
  • 2,090
  • 1
  • 19
  • 26