MySQL full-text search across multiple columns: confusing results

Question

I have a search query that does a full-text search in the database.

$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, but I know that there is an artist with two entries in db with artist_full_name = Cardozo, Horacio.

If I delete all MATCH fields and just al . artist_full_name get 2 results. If I add in al . description , I get 1 result because "Horacio Cardozo" exists in the description.

Is there a way for the search to return all records if any condition (any word of the search query) occurs in any of the MATCH fields? I tried to remove IN BOOLEAN MODE, but this led to the same results.

Answer 1

It seems that InnoDB tables do not allow you to search across multiple full-text indexes in the same MATCH() state.

Here, your fields do not all refer to the same table, so they are covered by different indexes. Note the same limitation if you have 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 seems that a full-text search can only search by the first full-text index it encounters, but this is just what I subtract from this experience , please do not take it for granted.

The bottom line is that you have to split your search in order to use one full-text index in MATCH() :

 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 have two different indexes on auction and one on user . You need to adapt it to your actual structure (please write descriptions of your tables if you need more recommendations).

Please note that this applies only to InnoDB tables. Interestingly, MyISAM tables do not seem to show the same restrictions .

---

Update: it turned out that this was a bug in the InnoDB engine , fixed in 5.6.13 / 5.7.2. The above example now fails rightly: "Cannot find the FULLTEXT index matching the column list." Indeed, there is no index on (f1, f2) , but one on (f1) and the other on (f2) . As reported in the change log :

Unlike MyISAM, InnoDB does not support logical full-text search on non-indexed columns, but this restriction has not been applied, as a result of queries that return incorrect results.

It should be noted that although such queries return the correct result set with MyISAM, they work more slowly than you would expect, since they silently ignore existing full-text indexes .