MySQL中更有效的词边界查询 [英] More efficient word boundary query in mySQL

问题描述

我有一个包含1/2万个短语的表，并且正在使用此查询进行单词匹配:

I have a table with 1/2 million phrases and I am doing word matching using this query:

SELECT * FROM `searchIndex` WHERE `indexData` RLIKE '[[:<:]]Hirt'

indexData字段具有FULLTEXT索引，并且数据类型为长文本.

The indexData field has a FULLTEXT index and is datatype longtext.

我想匹配诸如

"Alois M. Hirt"

"Alois M. Hirt"

以新角色显示Biz-Al Hirt ..."

"Show Biz - Al Hirt, in a new role, ..."

阿尔·希特(Al Hirt)的Sinatraville公开赛9点..."

"Al Hirt's Sinatraville open 9 p..."

将要播放Hirt ..."

"Hirt will be playing..."

而不是在衬衫"，十三"或三十"等上.

and not on "shirt" or "thirteen" or "thirty" etc.

查询成功，但是通常需要3秒钟才能返回，我想知道是否有更好，更有效的方法来进行单词边界匹配?

The query is succeeding but it frequently takes 3 seconds to return and I wondered if there was a better, more efficient way of doing this word boundary match?

如果我要向indexData添加另一个索引，那么要使用的正确密钥长度是什么?

If I were to add another index to indexData what would be the correct keylength to use?

TIA

推荐答案

setsuna 的答案效果很好:

SELECT * FROM searchIndex反对(indexData)反对(布尔模式下的"Hirt *")；

SELECT * FROM searchIndex WHERE MATCH (indexData) AGAINST ('Hirt*' IN BOOLEAN MODE);

这篇关于MySQL中更有效的词边界查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！