SQL 改进 Firebird 数据库上的“% LIKE %”查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9007269/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Improving '% LIKE %' Query on Firebird Database
提问by Marcello Grechi Lins
Situation:
情况：
I am trying to write a efficient query using "LIKE" statement to look after a piece of text into a column with short texts.
我正在尝试使用“LIKE”语句编写一个有效的查询来将一段文本放入带有短文本的列中。
Model:
模型：
Table 'EVENTSGENERAL' : { ID (KEY), GENERATOR_ (FK), DATETIME, COMPUTERNAME, OSLOGIN, DBLOGIN, INFOTYPE, INFO }
Table 'EVENTSGENERATORS' : { ID (KEY), GENERATOR_ (FK), SHORTNAME, LONGNAME }
Table 'EVENTSINFOTYPES' : { ID (KEY), GENERATOR_ (FK), VERSION_, INFOTYPE, DESCRIPTION }
Indexes : EVENTSGENERAL.GENERATOR, EVENTSGENERAL.DATETIME, EVENTSINFOTYPES.INFOTYPEAll Ascending.
索引 : EVENTSGENERAL.GENERATOR, EVENTSGENERAL.DATETIME,EVENTSINFOTYPES.INFOTYPE全部升序。
My Query:
我的查询：
SELECT FIRST @first SKIP @skip A.ID,B.LONGNAME, A.DATETIME, A.COMPUTERNAME,A.OSLOGIN, A.DBLOGIN, C.DESCRIPTION, A.INFO
FROM EVENTSGENERAL A JOIN EVENTSGENERATORS B ON B.GENERATOR_ = A.GENERATOR_
JOIN EVENTSINFOTYPES C ON C.GENERATOR_ = A.GENERATOR_ AND C.INFOTYPE = A.INFOTYPE
WHERE C.DESCRIPTION LIKE '%VALUE%'
Problem:
问题：
This query will run on extra large databases. Is there any way i can improve it ? I am using a Firebird database.
此查询将在超大数据库上运行。有什么办法可以改进吗？我正在使用 Firebird 数据库。
Thanks in advance.
提前致谢。
回答by Ken White
No, unfortunately not, because you use wildcards (%) on both sides of the LIKEkeyword. The leading (beginning) wildcard means that no index can be used to help improve the search speed, and therefore every row has to be examined to see if it meets the criteria.
不，不幸的是不是，因为您%在LIKE关键字的两侧都使用了通配符 ( ) 。前导（开始）通配符意味着不能使用任何索引来帮助提高搜索速度，因此必须检查每一行是否符合条件。
You can speed things up somewhat by changing to LIKE 'VALUE%'instead; an index can at least be used to limit the rows being searched to those starting with VALUE.
您可以通过更改为LIKE 'VALUE%'来加快速度；索引至少可以用于将被搜索的行限制为以 开头的行VALUE。
回答by Arjan
Full text search (using %VALUE%) is slow, because there is no efficient way to do that. It's like grabbing a dictionary to make a list of all words that containthe letter Q. That takes forever. On the other hand, if you just look for VALUE%it's a lot easier (if the search field is indexed). It's like making a list of all words that start withthe letter Q. That's easy if the words are alphabetically sorted (like they are in a dictionary). You can easily find the first and last words, and you can be sure that you need everything in between and nothing else.
全文搜索（使用%VALUE%）很慢，因为没有有效的方法来做到这一点。这就像拿一本字典来列出所有包含字母 Q 的单词。这需要很长时间。另一方面，如果您只是寻找VALUE%它会容易得多（如果搜索字段已编入索引）。这就像列出所有以字母 Q开头的单词。如果单词按字母顺序排序（就像在字典中一样），这很容易。您可以轻松找到第一个和最后一个词，并且您可以确定您需要介于两者之间的所有内容，而不是其他任何内容。
Some databases allow a full text search, they can index each word in a column, but only the full word. So searching for %q%still would be slow, and searching for q%would be faster. I'm not sure whether or not Firebird supports it.
一些数据库允许全文搜索，它们可以索引列中的每个单词，但只能索引完整的单词。所以搜索%q%仍然会很慢，搜索q%会更快。我不确定 Firebird 是否支持它。
If you have a large database and you want to be able to search for %q%, you should look into other, specialized solutions, like Luceneor Xapian
