如何索引在搜索词开头只有一个通配符的文本搜索（'％TERM'）？

I found the following query in our MySQL slow query log:

我在MySQL慢查询日志中发现了以下查询：

SELECT target_status 
FROM link_repository 
WHERE target_url LIKE CONCAT('%', 'bundle/rpi_/activity/rpi_bridge/bridge_manual.pdf')

When I pointed this out to the developer manager in a conversation about slow page loads, he stated:

当我在关于慢速页面加载的对话中向开发人员经理指出这一点时，他说：

come on; concat() is a simple string concatenation and '%' is the wildcard in the search string. I know that searching strings is not the fastest of operations (that's why we have lucene-like engines, but this is trivial stuff)

来吧; concat（）是一个简单的字符串连接，'％'是搜索字符串中的通配符。我知道搜索字符串并不是最快的操作（这就是为什么我们有类似lucene的引擎，但这是微不足道的东西）

There's about 18k rows in link_repository, which isn't much. The documentation I'm finding is that indexing on character strings doesn't work with wildcards. Is there an alternative strategy one can use?

link_repository中大约有18k行，这并不多。我发现的文档是对字符串的索引不适用于通配符。是否有可以使用的替代策略？

1 个解决方案

#1

In order for LIKE to use index it has to start with something. MySQL search from left to rigt. So if the string star with anything then MySQL will do a table scan and no index will work.

为了让LIKE使用索引，它必须以某些东西开头。 MySQL从左到右搜索。因此，如果带有任何内容的字符串明星将执行表扫描，并且没有索引可以工作。

However, if you are using InnoDB tables you can try to use Full-Text Index.

但是，如果您使用的是InnoDB表，则可以尝试使用全文索引。

You can add a Full-Text Index on the column, then you can use MATCH AGAINST function to find what you need then you can add RIGHT() clause to only give you the results that end with your string.

您可以在列上添加全文索引，然后您可以使用MATCH AGAINST函数找到您需要的内容，然后您可以添加RIGHT（）子句，只为您提供以字符串结尾的结果。

CREATE FULLTEXT INDEX target_url ON target_status(target_url);

Then you can query the records like so

然后你可以像这样查询记录

SELECT target_status 
FROM link_repository 
WHERE MATCH ('bundle/rpi_/activity/rpi_bridge/bridge_manual.pdf') AGAINST(target_url) AND RIGHT(target_url, 49) = 'bundle/rpi_/activity/rpi_bridge/bridge_manual.pdf'

#1