Mysql – Multiple match() against() in a MySQL query

full-text-searchindexMySQLperformancequery-performance

Same question asked on here, with no answers.

I'm using MySQL 5.7.17 on Windows 10.
I got this table:

CREATE TABLE `tbl1`(
  `id` int(10) unsigned NOT NULL,
  `col1` varchar(128) NOT NULL DEFAULT '',
  `col2` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `col1_idx` (`col1`) WITH PARSER `ngram`,
  FULLTEXT KEY `col2_idx` (`col2`) WITH PARSER `ngram`,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And I would like to make a query that col1 MUST have str1 and col2 MUST NOT have str2, I run multiple MATCH() AGAINST() in a query,like this

SELECT * FROM tbl1
 WHERE MATCH(col1) AGAINST('+str1' IN BOOLEAN MODE)
   AND MATCH(col2) AGAINST('-str2' IN BOOLEAN MODE);

But this query returns nothing.

When I use EXPLAIN, like:

EXPLAIN SELECT * FROM tbl1 WHERE 
 MATCH(col1) AGAINST('+str1' IN BOOLEAN MODE) 
   AND MATCH(col2) AGAINST('-str2' IN BOOLEAN MODE);

it returns:

+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table     | partitions | type     | possible_keys           | key           | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | tbl1      | NULL       | fulltext |    col1_idx, col2_idx   | col1_idx      | 0       | const |    1 |    11.11 | Using where; Ft_hints: no_ranking |
+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+

How do i make the query works? Any advice would be appreciated.

Best Answer

First, I think we need to assume that str1 and str2 occur in less than half the rows.

I think you will need to CREATE TEMPORARY TABLEs, one with list of id from one MATCH, one from the other. Create an index on id. Then JOIN the two tables.

It's messy, but FT wants to go first, yet you have two FT clauses in a single query. Hence my attempt to turn it into two queries.

Since you are using 5.7, and it has the smarts to create an index on a derived table:

SELECT t3.*
    FROM ( SELECT id FROM tbl1 WHERE MATCH ... +str1 ... ) AS t1
    JOIN ( SELECT id FROM tbl1 WHERE MATCH ... -str2 ... ) AS t2  USING(id)
    JOIN tbl1 AS t3  ON  USING(id);

Give it a try. -- I am guessing.

Related Question