MySQL InnoDB – Multicolumn Fulltext Search: SELECT MATCH Requires Single Column Index?

innodbMySQL

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

Best Answer

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.

Related Solutions

Mysql – No results for match…against in boolean mode search with fulltext index (MySQL)

I ended up asking a DBA, and he said that the comma is recognized as a word separator in MySQL. So, that meant that it was looking for "asp" and "net", both of which are under the 4 character minimum for words to be included in a fulltext index. I added the configuration directive ft_min_word_len=3 to my.cnf, restarted mysqld, and rebuilt the fulltext index on the table. Using MATCH(column-name) AGAINST ('+asp +net' IN BOOLEAN MODE); now locates the proper records.

Thanks everyone for your help!

Mysql – why/how does the number of matched columns influences the way of excecuting a query

I can provide with a general explanation, but it may not apply specifically to your particular case:

The way decision making works is by evaluation cost of execution plan, then picking up what is hopefully the cheapest plan. This you already know.

When it comes to indexing, though, stuff are getting interesting. The way to evaluate the usefulness or viability of an index is to estimate the selectivity given some value.

For the moment, forget about your FULLTEXT index, and let's assume a simple index on some column col1, and another index on some column col2. Given the following two queries:

SELECT * FROM t WHERE col1 < 10 and col2 = 4;
SELECT * FROM t WHERE col1 BETWEEN 100 AND 110 and col2 = 4;

It may happen that the query is evaluated differently in these two cases. Why? Because it may happen that col2 = 4 returns more rows than col1 < 10, in which case we prefer to use index on col1. But then, it may return less rows than col1 BETWEEN 100 AND 110, in which case we prefer the index on col2.

Your case is not very much different. MySQL estimates the number of rows returned by some index query. When you use more columns, MySQL gets the impression your index is likely to result with few rows. So it chooses to start with TableA, then joins what should be very few rows with TableB.

But if MySQL believes the index to return many rows, it may prefer starting with TableB. Why is that? Because you are sorting on indexed columns of TableB. Sorting is a lot of work, too. So MySQL may choose to first sort the rows, then join to TableA and filter by fulltext index. It may not be a bad idea if the fulltext search yields with many rows anyhow.

Best Answer

Related Solutions

Mysql – No results for match…against in boolean mode search with fulltext index (MySQL)

Mysql – why/how does the number of matched columns influences the way of excecuting a query

Related Question