CODEWITHSUNDEEP
mysqlsqlindexing
Ethan Allen
Ethan Allen

Reputation: 14835

How can I do a MATCH AGAINST with a FULLINDEX against two or more columns in MySQL?

I have the following query:

SELECT *
FROM pin
WHERE MATCH (title) AGAINST ('+red +yellow +blue' IN BOOLEAN MODE)
OR MATCH (front) AGAINST ('+red +yellow +blue' IN BOOLEAN MODE)

If I am correct, this will give me back results that have all words in either title OR front.

Is there a way to design the query so that I also get results where "red" could be in title and "yellow" and "blue" could be in description, or any other combination? I would like results for all three words that are in any combination of any of the columns (but it must have all three words, no less).

Upvotes: 0

Views: 38

Answers (1)

eggyal
eggyal

Reputation: 125955

Build a composite fulltext index over both columns:

ALTER TABLE pin ADD FULLTEXT(title, front);

SELECT *
FROM   pin
WHERE  MATCH(title, front) AGAINST ('+red +yellow +blue' IN BOOLEAN MODE);

Upvotes: 2

Related Questions