Mysql search engine like and match against

Question

I hate 4000+ adverts on a database. I use a FULLTEXT index on fields title, model and description.

In MySQL I change the value of ft_min_word_len from 4 to 3.

Actually, all the results are not matched.

Here is a simple request :

SELECT * ,
    MATCH (
        anno_modele, anno_titre, anno_desc
    )
    AGAINST (
        "330"
    ) AS relevance
FROM (
    `annonce`

JOIN possede
USING ( `anno_id` )
JOIN annonceur
USING ( `ann_id` )
JOIN cat_lang
USING ( `cat_id` )
JOIN lang_pays
USING ( `pays_id` )
JOIN marque
USING ( `mar_id` )
WHERE `mar_id` =867
AND MATCH (
    anno_modele, anno_titre, anno_desc
)
AGAINST (
    " 330"
 )
AND `cat_id`
IN (
    '3'
)
AND `anno_active` =1
AND `anno_mode` =1
AND `lang_pays`.`lang_id` = '3'
GROUP BY `anno_id`
ORDER BY `anno_prix` , `relevance` DESC
LIMIT 15

This matches me 3 results. 330 is just the field "anno_model".

If if do a like anno_modele LIKE '%330%', it matches me 9 results.

Here are the results matched by MATCH AGAINST :

enter image description here

Here are the results matched by LIKE

enter image description here

As you can see, when it exists a space ... the results are not matched by MACTH AGAINST

Is the problem on my request or it's something else ?

Help me please =)

You have a space before 330 in your second match against, i am assuming this space should be after — legrandviking, Feb 20 '13 at 15:54
When I delete this white space, nothing change. My goal is to match results contains "330" with or without space after before... In fact, I want the same result as LIKE — Raphaël, Feb 20 '13 at 16:25

score 2 · Accepted Answer · answered Feb 20 '13 at 16:32

2

Try using the * wildcard

...AGAINST ('*330*') ...

answered Feb 20 '13 at 16:32

legrandviking

2,348
1
22
29

is that it works on a more complete query... "blue train 330" ? Do I wildcard all the terms "\*blue\* \*train\* \*330\* ? – Raphaël Feb 20 '13 at 16:44
I would recommend something closer to this "*blue*train*330*" but this would result in content with ALL the words in that order. Usually using a against statement, mysql will split words in your sentence and give u points for each words. Visit http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html for more information on full search text – legrandviking Feb 20 '13 at 18:34
3

The wildcard works perfectly "IN BOOLEAN MODE" ("330*" IN BOOLEAN MODE). Thank you – Raphaël Feb 21 '13 at 09:13
Using the wildcard will often return fewer results. For example; in this query you are basically saying it MUST have atleast one "word" beginning with 330... – Oct 28 '14 at 15:27
Additional information regarding problems with wildcard [here](http://dba.stackexchange.com/q/21118/63206) – mgutt Apr 11 '15 at 20:29

Mysql search engine like and match against

1 Answers1