11

I'm trying to find "the zen" string in a field containing "The Zen Circus". I've got a FULLTEXT index.

select url,name,
,   MATCH(name) AGAINST ( 'zen*' IN BOOLEAN MODE) as A
,   MATCH(name) AGAINST ( '"the zen*"' IN BOOLEAN MODE) as B
,   MATCH(name) AGAINST ('>the* zen' IN BOOLEAN MODE) as C
,   MATCH(name) AGAINST ('thezen*' IN BOOLEAN MODE) as D
,   MATCH(name) AGAINST ('cir*' IN BOOLEAN MODE) as E
,   MATCH(name) AGAINST ('circus*' IN BOOLEAN MODE) as F
from pages where url='thezencircus'

I've got this result:

url = thezencircus
name = The Zen Circus
A = 0   (why?)
B = 0   (why?)
C = 0   (why?)
D = 0   (ok)
E = 1   (ok)
F = 1   (ok)

I've also putted ft_min_word_len = 2 in the msyql config file.

Any idea?

Pons
  • 1,747
  • 1
  • 13
  • 19

3 Answers3

11

See this answer MySQL full text search for words with three or less letters

[mysqld]
ft_min_word_len=3
Then you must restart the server and rebuild your FULLTEXT indexes.

Remeber to restart and rebuild indexes.

Edit
Run show variables like 'ft_%'; to confirm that the word length matches what you set it to.
Community
  • 1
  • 1
Kao
  • 2,242
  • 3
  • 22
  • 31
  • I've done everything you said, thank you, but the problem is still here. :-( – Pons Oct 25 '12 at 10:55
  • Try running REPAIR TABLE pages QUICK; Also, just to make sure you got it right, on setting the ft_min_word_length: http://lostquery.com/questions/196/how-do-i-enable-short-3-letter-full-text-search-in-mysql is this how you did it? – Kao Oct 25 '12 at 11:43
  • Also, see my edit on how to confirm the value of the ft_min_word_len – Kao Oct 25 '12 at 11:55
  • Stupid! It works! thank you for the support, the server was not restarted (the hosting provider told me he did it, but he didn't!) – Pons Oct 25 '12 at 12:03
2

Kao is right but that didn't help me at all - why? InnoDB table uses a different setting - try updating this one and checking it with:

    show variables like 'innodb_ft_min_token_size';
Scott
  • 695
  • 1
  • 8
  • 16
  • Yep, that was my issue. ft_min_word_len was already set to 2 before I even created my indexes, but setting innodb_ft_min_token_size as well and then restarting the server and deleting and recreating the index worked. Thanks! – Codemonkey Dec 03 '18 at 18:19
0

SELECT *, MATCH (title) AGAINST('$aranan*' IN BOOLEAN MODE) AS score FROM vod WHERE MATCH (title) AGAINST ('$aranan*' IN BOOLEAN MODE) ORDER BY score DESC LIMIT 10

YilmazTV COM
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 05 '22 at 16:58