MySQL fulltext search over multiple columns: result confusion

Question

I have a search query which performs a fulltext search on the DB.

$sql = "SELECT
*
FROM 
`tbl_auction_listing` AS `al` 
JOIN 
`tbl_user` AS `u` ON `al`.`user_id` = `u`.`user_id` 
LEFT JOIN
`tbl_gallery_details` AS `gd` ON `al`.`user_id` = `gd`.`user_id`
LEFT JOIN
`tbl_self_represented_details` AS `sr` ON `u`.`user_id` = `sr`.`user_id`
WHERE 
`al`.`status` = '" . ACTIVE . "'
AND
`al`.`start_date` < NOW() 
AND
`al`.`end_date` > NOW()
AND
MATCH(`al`.`listing_title`,
`al`.`description`,
`al`.`provenance`,
`al`.`title`,
`al`.`artist_full_name`,
`al`.`artist_first_name`,
`al`.`artist_last_name`,
`sr`.`artist_name`,
`gd`.`gallery_name`,
`u`.`username`) AGAINST('$search_query' IN BOOLEAN MODE)";

When I search for 'Cardozo, Horacio' or 'cardozo' or 'horacio' I get no results however I know there is an artist with 2 records in the db with artist_full_name = Cardozo, Horacio.

If I remove all MATCH fields and just have al.artist_full_name I get 2 results. If I add in al.description I get 1 result because 'Horacio Cardozo' exists in the description.

Is there a way to have the search return all records if any condition (any search query word) is met in any of the MATCH fields? I tried removing IN BOOLEAN MODE but that produced same results.

Answer 1

It appears that InnoDB tables do not allow searches over several fulltext indexes in the same MATCH() condition.

Here your fields do not all belong to the same table, therefore they are covered by different indexes. Notice the same limitation applies if you had a table like this:

CREATE TABLE t (
  f1 VARCHAR(20),
  f2 VARCHAR(20),
  FULLTEXT(f1), FULLTEXT(f2)
) ENGINE=InnoDB;

SELECT * FROM t
WHERE MATCH(f1, f2) AGAINST ('something in f2'); -- likely to return no row

It looks like a fulltext search may only search on the first fulltext index it encounters but this is only something I deduct from this experience, please do not take this for granted.

The bottomline is that you should split your search so as to use one single fulltext index per MATCH() clause:

SELECT * FROM auction, user, gallery, ...
WHERE
    MATCH(auction.field1, auction.field2) AGAINST ('search query' IN BOOLEAN MODE) OR
    MATCH(auction.field3) AGAINST ('search query' IN BOOLEAN MODE) OR
    MATCH(user.field1, user.field2, user.field3) AGAINST...

This is an illustration of a possible query if you had two distinct indexes on auction and one one on user. You need to adapt it to your actual structure (please post your tables' descriptions if you need more guidance).

Notice this only applies to InnoDB tables. Interestingly, MyISAM tables do not seem to show the same limitation.

---

Update: it turns out this was a bug in the InnoDB engine, fixed in 5.6.13/5.7.2. The above example now rightfully fails with "Can't find FULLTEXT index matching the column list". Indeed, there is no index on (f1, f2), but one on (f1) and another one on (f2). As the changelog advises:

Unlike MyISAM, InnoDB does not support boolean full-text searches on nonindexed columns, but this restriction was not enforced, resulting in queries that returned incorrect results.

It is noteworthy that while such queries return a correct result set with MyISAM, they run slower than one might expect, as they silently ignore existing fulltext indexes.