1

I have table with two fields and run a fulltext query with weighted order:

SELECT 
  MATCH (`my_table`.`text_1`) AGAINST ('+lorem' IN BOOLEAN MODE) AS `reltext_1`, 
  MATCH (`my_table`.`text_2`) AGAINST ('+lorem' IN BOOLEAN MODE) AS `reltext_2`, 
  my_table.id
FROM 
  my_table my_table 
WHERE 
  (
    MATCH (
      `my_table`.`text_1`,
      `my_table`.`text_2`
    ) AGAINST ('+lorem' IN BOOLEAN MODE)
  ) 
ORDER BY 
  (2 * `reltext_1` + `reltext_2`) DESC

There's one multicolumn index containing both fields text_1 and text_2. This was a MyISAM table in the past and everything run fine.

Now when I switch the table over to InnoDB I see a Can't find FULLTEXT index matching the column list error. What I figured out is that everything works again if I add two additional single column indices for text_1 and text_2. Is this really necessary? Do I do something wrong?

Edit: Tested on MySQL 5.6.38

0

Alas that is one of a few differences between the two separate implementations of FULLTEXT.

Alas. the workaround is to add two more indexes:

FULLTEXT(text_1)
FULLTEXT(text_2)

But... Don't count on relevance numbers coming back from different indexes as being comparable.

| improve this answer | |

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.