我需要在两端使用 % 实现通配符搜索，如何提高性能? [英] I need to implement wildcard search using % on both ends, how can I improve performance?

问题描述

我将在 sql 端实现这个，所以我最终会得到类似

I will be implementing this in the sql side, so I would end up having something like

where company.name like '%:parameter%'

我之前读过，这将使数据库无法使用 company.name 索引.

I've read before that this would then make it impossible for the DB to use the company.name index.

我的问题是，我还有什么其他选择可以最大限度地减少这将引入搜索的性能下降?请注意，这是客户要求，因此我无法选择不实施(即使我向他们解释了这样做的性能后果).

My question then would be, what other options do I have to minimize the performance degradation that this will introduce to the search? Please note that, this is client requirements therefore I don't have the option to not implement (even though I explained to them the performance consequence of this).

应用程序使用的是 Sybase SE 12.5.3(基于 DBArtisan 8.5.5 中使用的驱动程序)

The application is using Sybase SE 12.5.3 (based on the driver used in DBArtisan 8.5.5)

推荐答案

我不知道 Sybase，但您可以按照以下方式做一些事情:

I don't know Sybase, but you could do something along the lines below:

• 创建第二个字段，与公司名称相反.
• 在这个新字段上添加索引

• 使用以下 where 子句

• Create a second field, which is the reverse of the company name.
• Add an index on this new field

• Use the following where clause

WHERE company_name like ':parameter%' 或 reverse_name like反向(参数)+'%'

WHERE company_name like ':parameter%' or reverse_name like Reverse(parameter)+'%'

希望这能给你指明一个好的方向

Hope this points you in a good direction

这篇关于我需要在两端使用 % 实现通配符搜索，如何提高性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！