CODEWITHSUNDEEP
mysqlfull-text-search
BadHorsie
BadHorsie

Reputation: 14544

FULLTEXT search not working on second column

I am trying to do a FULLTEXT search against two columns. The first column holds subject names, the second column aliases, such as:

|            name            |      also_match      |
-----------------------------------------------------
|   Information Technology   |   IT I.T.            |
|   Mathematics              |                      |
|   English                  |                      |
|   Religious Studies        |   RS R.S. RE R.E.    |

Schema details:

These columns are both VARCHAR(100) and I have tried multiple types of FULLTEXT indexes. I have tried separate indexes for both of the columns, and I've tried an index that covers both columns together.

The search works fine at returning results based on the name column, but nothing turns up from the also_match column.

Example query that returns 1 result:

SELECT *
FROM subjects
WHERE MATCH(s.name, s.also_match) AGAINST ('*Information*' IN BOOLEAN MODE)
LIMIT 20

Example query that returns 0 results:

SELECT *
FROM subjects
WHERE MATCH(s.name, s.also_match) AGAINST ('*IT*' IN BOOLEAN MODE)
LIMIT 20

Upvotes: 0

Views: 223

Answers (2)

Gustav Bertram
Gustav Bertram

Reputation: 14901

I think your problem is that MySQL does not index words shorter than a certain length. That length is specified in the ft_min_word_len variable.

For example, assuming your table is this:

+------------------------+------------+----+
| name                   | also_match | id |
+------------------------+------------+----+
| Information Technology | IT I.T.    |  1 |
| Mathematics            |            |  2 |
| Religious Studies      | RS         |  3 |
| Computer Studies       | C.S. CSSS  |  4 |
+------------------------+------------+----+

Then the following query works:

SELECT * FROM `fulltext` f 
WHERE MATCH (also_match) AGAINST ('CS*' IN BOOLEAN MODE);

+------------------+------------+----+
| name             | also_match | id |
+------------------+------------+----+
| Computer Studies | C.S. CSSS  |  4 |
+------------------+------------+----+

If you make ft_min_word_len shorter, then it unfortunately affects the entire server, and is likely to make FULLTEXT indexes much larger.

You might have to resort to using LIKE on also_match when you detect really short keywords, or build your own fulltext search.

One of these fulltext search engines might also be able to solve your problem:

Upvotes: 0

Vladislav Rastrusny
Vladislav Rastrusny

Reputation: 29965

Are you sure you are using asterisk correctly in BOOLEAN MODE?

The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it should be appended to the word to be affected. Words match if they begin with the word preceding the * operator.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

Upvotes: 0

Related Questions