0

I have some trouble with a MySQL table and a fulltext index. My table structure looks similar to this:

CREATE TABLE example_index (
    id int(11) NOT NULL auto_increment,
    title tinytext,
    content text,
    FULLTEXT INDEX title (title),
    FULLTEXT INDEX titlecontent (title,content),
    PRIMARY KEY (id)
)ENGINE=MyISAM;

As you can see I have created a fulltext index for the fields title and title and content in combination. Furthermore I have stored some data into this table like this:

id | title      | content
1  | Teamwork   | Example data
2  | CSV-Export | Testdata

If I try to get some data from this table via a match against query then I got one result for the following query:

SELECT *
FROM example_index
WHERE MATCH (title) AGAINST('csv' IN BOOLEAN MODE);

BUT no results for this query:

SELECT *
FROM example_index
WHERE MATCH (title) AGAINST('Team' IN BOOLEAN MODE);

Can someone tell me, why I got no results here?

Fox
  • 623
  • 8
  • 35

2 Answers2

2

MySQL fulltext indexes index words. This is a very important limitation. You do not have the word team in your data, but you do have the word csv because - is considered a word delimiter.

If you are looking for words starting with team then you can still use the fulltext index using the * operator within the string to be searched:

SELECT *
FROM example_index
WHERE MATCH (title) AGAINST('Team*' IN BOOLEAN MODE);

If you would like to get records that contain the substring team anywhere within the indexed field, then MySQL fulltext index and fulltext search cannot help you. In this case you either need to revert to the good old title like '%team%' or you need to use a different fulltext search provider that can use MySQL.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Yeah ok thank you. I also tried this solution for some minutes ago and this works fine for me. Can you tell me if this is still more performant then a like query? – Fox Mar 17 '17 at 10:30
  • It depends. If the word `team` is always at the beginning of the field, then you can use `title like 'team%'`, which can utilise a traditional index on a field. In this case `like` may even be faster than a fulltext search. If the word starting with `team` can be anywhere within the field, so you need to use `title like '%team%'`, then `like` cannot use an index, so will be slower than a fulltext search. – Shadow Mar 17 '17 at 10:38
  • Ok thank you :). Something like this would be great: '\*team\*', but this not working :(. So I have to use like in this case or I have to fill the content field for this fields like title: teamwork and content: team, work. So I could still use a match against query. – Fox Mar 17 '17 at 10:38
  • "Something like this would be great: '*team*', but this not working :(. " - yeah, I told you that in my answer... – Shadow Mar 17 '17 at 10:40
0

Full text search looks for words in the text not partial words. Because your text contains "Teamwork", it does not contain team. This is a limitation of many full text implementations.

If your data is not too big, you can use like:

SELECT *
FROM example_index
WHERE title LIKE '%Team%';

If you are just looking for titles that start with "Team", you can do:

SELECT *
FROM example_index
WHERE title LIKE 'Team%';

The advantage of this approach is that it will take advantage of a regular index on example_index(title).

I notice that MySQL now has an n-gram parser. This parser is designed for non-English (primarily east Asian) languages. However, it might work on English as well. You might be able to try this to do what you want.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hm ok thx, but I think "like" is not the right thing for me, because my table will be contain a lot of rows in future. So like will be not performant enough to handle this. I have seen, that I could also use wildcards for fulltext index like this: AGAINST('Team*' IN BOOLEAN MODE) and then I got a result, too. But I dont know if this is better then like, maybe you know something about this? – Fox Mar 17 '17 at 10:28
  • @Fox . . . Right, I forgot that MySQL supports that. Of course, that should work better than `like`. I think it will use the dictionary to expand the search terms to include all matching words in the dictionary. – Gordon Linoff Mar 17 '17 at 11:27