开发者

Fulltext index questions

I'm trying to implement a fulltext search on my database and got some problems I'm not sure how to solve..

  1. First I used alter table when I created the fulltext indexes, when I finall开发者_如何学运维y got it to work I couldn't search for records that had been in the table before I created the indexes. Can the problem have anything to do with numbers? They were all mixed with numbers and letters.

  2. Anyway I continued, added some new records and then I created some new indexes, this time with phpMyAdmin. Now I still had the problem with the first records from 1. but the new records that was added before the new indexes worked fine. This make no sense (except if numbers isn't allowed), anyone have any ideas?

  3. Still continuing, I started testing with some more indexes. And this might be a little hard to understand, its easier if you worked with phpMyAdmin:

    In phpMyAdmin your indexes are "grouped" if you create them at the same time. This also happen when I used alter tables. For example, let say I have indexed the columns called name and summary. Now I can't make a search MATCH(name) AGAINST('foobar') but I can make the search MATCH(name, summary) AGAINST('foobar'). But if I create one more index, only with 'name' and keep the old "index group" the searches above works fine. And of course a search for MATCH(summary) AGAINST('foobar') won't work. Now why is that? And how do I fix it? (other than create a new index set for every possibly search I want to do)

Would love to get these question answered! Thank you


For #1 and #2, you should check the value of ft_min_word_len. By default words shorter than 4 letters are not indexed, per the manual.

For #3, this is a clearly documented restriction in the MySQL manual:

  • The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on nonindexed columns, although they are likely to be slow.
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜