0

I have a table with users and their addresses. Some flats looks like "p06"

This query does not find anything:

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +06' IN BOOLEAN MODE);

This query finds "Washington street, h.36, fl.p06":

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +p06' IN BOOLEAN MODE);

But this query finds only Washington street, h.36, fl.6:

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +6' IN BOOLEAN MODE);

This query works well and finds Washington street, h.36, fl.16:

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +16' IN BOOLEAN MODE);

p.s. ft_min_word_len = 1

I wish to get all results by query "+Wash* +36 +6". And find "Washington street, h.36, fl.p06" by query "+Wash* +36 +06"

Sure it could be wrong queries for my task

1
  • May be you should use +Wash* +36 +*06 – DEarTh Apr 14 '18 at 7:28
0

Are you using MyISAM or InnoDB?

What is the min-word-length?

In InnoDB, with innodb_ft_min_token_size > 2, the +xx leads to finding nothing. Here's the logic: + says that it must find the the word xx. However the token size says that the "word" xx is not indexed.

Note: I think MyISAM was more lenient.

One workaround: When building the AGAINST string, don't prepend + to any 'word' shorter than min-token-size. (I actually have an app that uses this trick; I am happy with the results.)

*06 does not work in FULLTEXT

Under no circumstances can you get FULLTEXT to find pxx by searching for xx.

The 0 is irrelevant. (If it is relevant, please rephrase the Question with a suitable example.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.