0

I'm having trouble with the match against function. When I run the following query :

    SELECT 
        i.name,
        n.trad,
        i.icon_name,
        i.quality
    FROM strings_item AS n
        LEFT JOIN items AS i ON n.name = i.description
    WHERE match(n.body_en) against ('+cat*+queen*' IN BOOLEAN MODE)

It returns rows that contains cat OR queen. Isn't + supposed to return rows containing both words?

Edit:

Here are some examples of data it returns :

Cataclysm's Jaws
Catalium
Catalium Rod
Grand Master's Gleaming Catalium Dagger

And it seems like it never returns anything with queen. So, I tried to change the matching word to '+cat +queen' and here is what it displayed :

Iceheart Queen's Shield
Queen Klaw's Pauldrons
[Playing Card] Queen of Hearts
Cat Queen Egg
Cat Queen Box

PS: I kept these words in the same order as in mysql and the row I'm looking for is the Cat Queen Egg

loumi
  • 95
  • 2
  • 12
  • 1
    It should work like you expect. Can you try to add a blank between +cat* and +queen* – Simon Martinelli Apr 02 '15 at 08:49
  • It returns no result when I put a blank between these two words. – loumi Apr 02 '15 at 08:52
  • This link might help you http://stackoverflow.com/questions/792875/which-sql-query-is-better-match-against-or-like – Ankit Apr 02 '15 at 08:54
  • When you suffix `*` data with then it will look data as `cats,cate,cati...` etc and same for queen. Not sure what you are getting but some sample data would be good to diagnose the issue. – Abhik Chakraborty Apr 02 '15 at 08:55
  • Your query seems to be correct after adding a `blank space` like `+cat* +queen*`. Does your database contains a row that have both `cat` and `queen` in it? – Ankit Apr 02 '15 at 09:00
  • I've edited the question. Yes, there is a row containing both : Cat Queen Egg. However, when I try +cat* +queen*, it returns nothing :/ – loumi Apr 02 '15 at 09:08
  • 1
    Well in your second matching query `+cat +queen` it is ingnoring `cat` as it is a 3 letter word and sql ignores them. Check ` ft_min_word_len` settings You will get the same result if you look for `queen` only. Just try `+cat* +queen` – Ankit Apr 02 '15 at 09:19
  • got your problem, just enter a row `category is queen` and try your match `+cat* +queen*`. It will return this row only. what's happening is, your database have a row `cat queen egg`. Sql do not take `cat` in its full index search as `cat` is too small. That is why you get nothing returned at present – Ankit Apr 02 '15 at 09:27
  • I tried +cat* +queen, it returned nothing. However, when I changed ft_min_word_len settings to 2, it worked fine. Thank you :) – loumi Apr 02 '15 at 09:30

1 Answers1

0

Your query is correct. Your database do not have a row that contains both cat* and queen*.

By default the ft_min_word_len has a value of 4 in sql. (https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_ft_min_word_len).

Therefore sql do not take words less than 4 characters in its full-text index.

eg:

Instructions      1
are               2
applicable        3
to                4
these             5
Adventure         6

In this row, sql will not take words 2,3 and 4 in its full-text index.

Either change your ft_min_word_len or add rows that contains word of more than 4 characters that contains cat in it.

Just to add here you can find a list of stop words that are also ignored in full-text index. http://mssqltipsandtricks.blogspot.de/2012/07/noisestop-words-in-sql-server.html

Ankit
  • 1,075
  • 1
  • 8
  • 19