mysql 列比较多的时候乱,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.

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.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值