mysql search query code issue

Question

This is my code to perform a search

SELECT d.deal_id,
       d.deal_title,
       d.friendly_url
FROM wp_deals AS d
WHERE MATCH (d.deal_title) AGAINST ('Ultimate Revitalisation Kit' IN BOOLEAN MODE)
GROUP BY d.deal_id

It works fine.

But when I search for Kit it becomes

SELECT d.deal_id,
       d.deal_title,
       d.friendly_url
FROM wp_deals AS d
WHERE MATCH (d.deal_title) AGAINST ('Kit' IN BOOLEAN MODE)
GROUP BY d.deal_id

and does not give any result although kit was there in the previous search result. I am not an expert with this type of search query. But I have to asked that search query should not be using like keyword i.e like '%kit%'. So that's why I am using against and match. Is anybody expert in this type of advanced query? Can you make it an outstanding search query? Please make it perfect. So it looks better than simple like query.

any result if you change `'Ultimate Revitalisation Kit'` to `'Ultimate%Revitalisation%Kit'` ? — ClydeFrog, Jan 10 '13 at 13:01
saw this [earlier thread](http://stackoverflow.com/questions/792875/which-sql-query-is-better-match-against-or-like) where the person is searching after the keywords `foo` and `bar` like this `MATCH (t1.foo_desc, t2.bar_desc) AGAINST ('+foo* +bar*' IN BOOLEAN MODE)` — ClydeFrog, Jan 10 '13 at 13:05
i am not changing 'Ultimate Revitalisation Kit' to 'Ultimate%Revitalisation%Kit'.I am talking about that when i search with Ultimate Revitalisation Kit.it gives results.and one result is "Ultimate Revitalisation Kit".but when i search with single keyword i.e Kit it gives nothing. — Rana.Asif, Jan 10 '13 at 13:24
possible duplicate of [MYSQL Match example not working. Is "Spa" a stopword?](http://stackoverflow.com/questions/9909460/mysql-match-example-not-working-is-spa-a-stopword) — Jocelyn, Jan 10 '13 at 13:55

score 1 · Answer 1 · 2013-01-10T13:50:00.733

1

You need to change your /etc/my.conf of your mySQL installation. By default fulltext searching only uses 4 characters for the minimum word length. You need to change this to 3.

ft_min_word_len = 3

You will also need to rebuild the fulltext index on your table. You can do this by running a REPAIR command:

REPAIR TABLE my_table QUICK;

Or you can delete and recreate the index.

Do this and restart mysql. Your search will now work.

edited Jan 10 '13 at 13:50

answered Jan 10 '13 at 13:23

I am using xamp.where it is there? – Rana.Asif Jan 10 '13 at 13:34
MySQL Conf File (my.cnf): C:\Program Files\xampp\mysql\bin\my.cnf – Jan 10 '13 at 13:35
Your full text indexes will have to work a little harder to get all of the 3 letter words too, but otherwise 3 letter word searches won't appear in results. – Jan 10 '13 at 13:36
+1: this is the correct answer (having said that, there are good reasons why full text search defaults to a minimum of four characters; changing it to three may fix the problem, but it may not be good for performance) – SDC Jan 10 '13 at 13:39
yes ok thats fine with 4 letters.But strange it also not working with "plus" or "Four" as a keyword.Although theya re there in deal_title.. – Rana.Asif Jan 10 '13 at 13:51

mysql search query code issue

1 Answers1