MySql Full text Search

Question

In my Job listings site, I need to show jobs which have similar titles to the one which is being viewed. I am trying the following query, but its not working:

SELECT  *, 
MATCH(title) AGAINST "Sales Coordinator" as relevance
FROM
  jobs
WHERE
  MATCH(title) AGAINST "Sales Coordinator"
ORDER BY relevance DESC
LIMIT 100

Also, can this be optimized, so as to give better results and maybe faster too?

Answer 1

You really need to clarify what is not working (i.e. what is it not finding) and how fast the current is. I assume you created a full text index on the "title" field?

You might be looking for the "IN BOOLEAN MODE" option.

MATCH(title) AGAINST ("Sales* Coordinator*" IN BOOLEAN MODE)

That would find things like "salesman".