INDEX如何减慢LIKE查询的速度？

This is a duplicate neither of MySQL index slowing down query nor of Slow search by index query LIKE% MYSQL

这既不是MySQL索引减慢查询也不是索引查询LIKE%MYSQL的慢搜索

On a very simple table, I tried a very simple query, with and without a specific index.

在一个非常简单的表上,我尝试了一个非常简单的查询,有或没有特定的索引。

Table (40 000 lines):

表(40 000行):

id                      int(11)     
shipping_address_id     int(11)     
billing_address_id      int(11)     
label                   varchar(100)
code                    varchar(15) 
email                   varchar(100)

Query : SELECT email FROM table WHERE email LIKE "%yahoo%"

查询:SELECT email FROM table WHERE email LIKE“%yahoo%”

Without an INDEX on "email", it takes 0.0035s. With this INDEX, however, it takes 0.021s, which is 7x slower.

如果没有“电子邮件”上的INDEX,则需要0.0035秒。但是,使用此INDEX需要0.021秒,这比需要慢7倍。

Yet the same INDEX does accelerate an equality query by 50x (WHERE email = "me@yahoo.com").

然而,相同的INDEX确实将等同查询加速了50倍(WHERE email =“me@yahoo.com”)。

So, why is the LIKE query negatively affected by the INDEX?

那么,为什么LIKE查询会对INDEX产生负面影响?

EDIT TYPO: The query originally stated LIKE "yahoo"while it should read LIKE "%yahoo%", my apologize.

编辑TYPO:查询最初说LIKE“yahoo”,而它应该读为LIKE“%yahoo%”,我道歉。

EDIT FOR CLARITY: the table is InnoDB, and i am comparing speeds of the same queries. In the first case (the "LIKE" case), the query is 7x slower with an INDEX on the compared field. In the second case ("=") the query is 50x faster with the INDEX.

编辑CLARITY:表是InnoDB,我正在比较相同查询的速度。在第一种情况下(“LIKE”情况),在比较字段上使用INDEX,查询速度慢7倍。在第二种情况下(“=”),使用INDEX,查询速度提高了50倍。

1 个解决方案

#1

The index implicitly solves (and speeds up) queries with comparison operators: =, <, >. If you have an index on the email field, and a LIKE query, MySQL has still to retrieve all the elements in the index, and apply the regexp to them.

索引使用比较运算符隐式地解决(并加速)查询:=,<,>。如果您在电子邮件字段和LIKE查询上有索引,MySQL仍然要检索索引中的所有元素,并将正则表达式应用于它们。

If the query was

如果查询是

 WHERE email LIKE 'yahoo%com'

you could speed it up by observing that email must be between 'yahoo' and 'yahoo[' (let's assume that [ comes after z in lexicographic order, and can not appear in the email field). Then changing the query to

你可以通过观察电子邮件必须介于'yahoo'和'yahoo ['之间来加快速度(让我们假设[按字典顺序排在z之后,并且不能出现在电子邮件字段中)。然后将查询更改为

WHERE (email BETWEEN 'yahoo' AND 'yahoo[') AND email LIKE 'yahoo%com'

would yield a benefit, because the index would quickly pre-select a smaller set of rows, and then the regexp would be run against those only (on a field that's already available, because it's in the index, so no table lookups are needed).

会产生一个好处,因为索引会快速预先选择一组较小的行,然后regexp将仅针对那些行运行(在已经可用的字段上,因为它在索引中,因此不需要查找表) 。

But in this case, in practice, MySQL has to do a full table scan; except that it is doing so on the index. The cost of doing this can be actually higher than that of a "true" table scan. In some cases MySQL would realize this, and you would see that the index is then actually not used by the query at all (depending on how recently it has been analyzed, its size, and other factors), and MySQL prefers to employ a full table scan.

但在这种情况下,实际上,MySQL必须进行全表扫描;除了它在索引上这样做。这样做的成本实际上可能高于“真正的”表扫描。在某些情况下,MySQL会意识到这一点,你会看到该索引实际上根本没有被查询使用(取决于它最近的分析,它的大小和其他因素),MySQL更喜欢使用完整的表扫描。

The other case in which an index might help you, by gathering data needed for the query, does also not apply because you are only asking for the email field, which is the one computation is being done on. So again the index yields no performance increase whatsoever.

索引可能通过收集查询所需的数据来帮助您的另一种情况也不适用,因为您只询问电子邮件字段,这是正在进行的计算。因此,该指数再次没有产生任何性能提升。

If you want an index that speeds up this kind of WHERE, you need a FULLTEXT index, not a "ordinary" index.

如果你想要一个加速这种WHERE的索引,你需要一个FULLTEXT索引,而不是一个“普通”索引。

#1