CODEWITHSUNDEEP
mysqlsqlfull-text-search
Get Off My Lawn
Get Off My Lawn

Reputation: 36341

How to split a FULLTEXT index between multiple match against

I have the following full text index:

FULLTEXT INDEX `title_short_description_description` (`title`, `short_description`, `description`)

When I run the below query I get this error:

Can't find FULLTEXT index matching the column list

select (
    (match (title) against ('abc' in boolean mode) * 100) +
    (match (short_description, description) against ('abc' in boolean mode))
) as score

from game_cache

where

    match (title) against ('abc' in boolean mode) or 
    match (short_description, description) against ('abc' in boolean mode)

order by score desc

I assume that this is because I am not using all three columns in the same match, but instead separating them between 2 matches. I have tried separating the indexes, but MySQL doesn't allow for that.

Is there any way I can make this query work?

Upvotes: 1

Views: 394

Answers (1)

Ike Walker
Ike Walker

Reputation: 65577

You need to have a separate index for each distinct set of columns you want to query using MATCH.

In order to run your example query, you need to add two more FULLTEXT indexes (one at a time):

ALTER TABLE your_table
  ADD FULLTEXT INDEX `index_on_title` (`title`);

ALTER TABLE your_table
  ADD FULLTEXT INDEX `index_on_short_description_and_description` 
    (`short_description`, `description`);

If you never run any queries that checks for matches on all 3 columns, then you should consider dropping your existing FULLTEXT index:

ALTER TABLE your_table
  DROP INDEX `title_short_description_description`;

Upvotes: 2

Related Questions