I want to make one column more valuable than others like it's been done in query bellow.This example shows that only one fulltext index is needed And this answersuggests that three fulltext indexes are needed to do the same.

Here is the queries from the first link:

ALTER TABLE products ADD FULLTEXT(title, sdescription, ldescription)

MATCH(`title`) AGAINST ('+iphone +case +4s' IN BOOLEAN MODE) * 10 as rel1,
MATCH(`sdescription`) AGAINST ('+iphone +case +4s' IN BOOLEAN MODE) * 3 as rel2,
MATCH(`ldescription`) AGAINST ('+iphone +case +4s' IN BOOLEAN MODE) as rel3,
FROM products
WHERE MATCH (title, sdescription, ldescription) 
  AGAINST ('+iphone +case +4s' IN BOOLEAN MODE)
ORDER BY (rel1)+(rel2)+(rel3) DESC;

I'm confused how many indexes are needed for that, one or three? I asked this question on stackoverflow but no answer yet.


MyISAM? or InnoDB?

4 FULLTEXT indexes will work in either engine.

You could find out by creating an empty table and running that SELECT against it. It should give you an error if the set of indexes you tried is wrong. The ALTER to add/drop indexes.

  • 1
    Rick, what I'm not understanding is why more than one indexes been suggested in that stackoverflow answer? This query is from that stackoverflow answer SELECT id, keyword, content, MATCH (keyword) AGAINST ('watermelon') AS rel1, MATCH (content) AGAINST ('watermelon') AS rel2 FROM table WHERE MATCH (keyword,content) AGAINST ('watermelon') ORDER BY (rel1*1.5)+(rel2) You can see that this query and the query in my question are doing the same thing, "making one column more valuable". Is the answer on stackoverflow wrong? – user3082321 Jun 13 '15 at 5:01
  • 1
    I think that MyISAM allowed use of a single 3-column index for BOOLEAN MODE for your query, but InnoDB needs 4 separate indexes. I have made a stab at the differences in my blog. The answer you saw probably failed to note the Engine. – Rick James Jun 13 '15 at 17:20
  • thanks @Rick James. I forgot to mention last time that I will use InnoDB. The query I posted in my question uses MYISAM. It's from the first link I posted in my question. – user3082321 Jun 16 '15 at 3:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.