CODEWITHSUNDEEP
mysqlindexing
Ninj
Ninj

Reputation: 1522

How can INDEX slow down a LIKE query?

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

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

Table (40 000 lines):

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%"

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

Yet the same INDEX does accelerate an equality query by 50x (WHERE email = "[email protected]").

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

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

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.

Upvotes: 1

Views: 389

Answers (1)

LSerni
LSerni

Reputation: 57398

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.

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

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).

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.

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.

Upvotes: 1

Related Questions