MySQL全文搜索多列:结果混乱(MySQL fulltext search over multiple columns: result confusion)

16 IT屋

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.

解决方案

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.

我有一个搜索查询,对数据库执行全文搜索。



  $ sql =" SELECT 
*
来自
`tbl_auction_listing` AS`al`
加入
`tbl_user` AS`u` ON`al`.`user_id` =`u`.`user_id`
左加入
tbl_gallery_details AS作为gd在ON上user.id = gd user_id`
左加入
tbl_self_representeded_details AS作为sr ON `u`.user_id` =`sr`.`user_id`
WHERE
`al`.`status` ='"。主动的。 "'

`al`。`start_date`< NOW()

`al`.`end_date`> NOW()
AND
MATCH(`al`.`listing_title`,
`al`.`描述`,
`al`。来源`,
`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)";


当我搜索" Cardozo,Horacio"或" cardozo"或" horacio"时,没有结果但是我知道数据库中有一位艺术家有2条记录,并且artist_full_name = Cardozo,Horacio。



如果我删除了所有MATCH字段,并且只有 al 。 artist_full_name 我得到2条结果。如果我添加 al 。说明,我会得到1条结果,因为说明中存在" Horacio Cardozo"。



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


解决方案

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



在这里,您的字段并不全部属于同一表,因此它们被不同的索引覆盖。请注意,如果您有这样的表,则存在相同的限制:



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

选择*从t
匹配(f1,f2)反对(" f2中的某物"); -可能不返回任何行


看起来可能是全文搜索仅在遇到的第一个全文索引上进行搜索,但这只是我根据这次经验得出的 / a> ,请不要认为这是理所当然的。



最重要的是,您应该拆分搜索,以便每个< code> MATCH()子句:



  SELECT * FROM拍卖,用户,画廊, ... 
在哪里
MATCH(auction.field1,Auction.field2)反对('搜索查询'在布尔模式下)或
MATCH(auction.field3)反对('搜索查询'在布尔模式)或
匹配(user.field1,user.field2,user.field3)反对...


如果您在拍卖上有两个不同的索引,而在 user 。您需要根据实际结构进行调整(如果需要更多指导,请发布表的说明)。



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






更新:原来这是 InnoDB引擎中的错误,已在5.6.13 / 5.7.2中修复。上面的示例现在正确地失败,并显示"找不到与列列表匹配的FULLTEXT索引"。实际上,在(f1,f2)上没有索引,但是在(f1)上有一个索引,而在< code>(f2)。 正如变更日志所建议的




与MyISAM不同,InnoDB不支持对
非索引列进行布尔型全文本搜索,但是未实施此限制,因此
的查询返回了错误的结果。




值得注意的是,尽管此类查询使用MyISAM返回了正确的结果集,但它们它们默默地忽略了现有的全文索引


本文地址:IT屋 » MySQL全文搜索多列:结果混乱