MySQL全文搜索多列：结果混乱

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.

当我搜索'Cardozo,Horacio'或'cardozo'或'horacio'时,我没有得到任何结果,但我知道在db中有一个艺术家有2条记录,其中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.

如果我删除所有MATCH字段并且只有al.artist_full_name,我得到2个结果。如果我添加al.description我得到1个结果,因为'Horacio Cardozo'存在于描述中。

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.

如果在任何MATCH字段中满足任何条件(任何搜索查询字),是否有办法让搜索返回所有记录?我尝试删除IN BOOLEAN MODE但产生了相同的结果。

1 个解决方案

#1

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

似乎InnoDB表不允许在相同的MATCH()条件下搜索多个全文索引。

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:

底线是你应该拆分你的搜索,以便每个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 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.

请注意,这仅适用于InnoDB表。有趣的是,MyISAM表似乎没有显示出相同的限制。

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:

更新:事实证明这是InnoDB引擎中的一个错误,修复于5.6.13 / 5.7.2。上面的示例现在正确地失败,“找不到与列列表匹配的FULLTEXT索引”。实际上,(f1,f2)上没有索引,但是(f1)上有一个索引,(f2)上有另一个索引。正如变更日志所建议:

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.

与MyISAM不同,InnoDB不支持对非索引列进行布尔全文搜索,但未强制执行此限制,导致查询返回不正确的结果。

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.

值得注意的是,虽然此类查询使用MyISAM返回正确的结果集,但它们运行速度比人们预期的要慢,因为它们默默地忽略现有的全文索引。

#1