CODEWITHSUNDEEP
mysqlsqldatabase-performancesql-like
chiperortiz
chiperortiz

Reputation: 4991

MySQL like performance on OR using index is better than %%?

Is better use this SQL code suppose the right index in apply on the column!!

Suppose constant is a input from a textfield!!

select ...
from .....
where lower(column) like 'Constant%' or lower(column) like '%Constant%'

Is better than?

select ...
from .....
where lower(column) like '%Constant%'

In the first code i try to match a "constant" using like but using a index trying being lucky to find a match and later i try to do a full match!!

All i want is my performance is not decreased! I mean if both queries runs in the same time or if the query can sometimes get a performance upgrade is OK with me

I use lower because we use DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Upvotes: 1

Views: 2078

Answers (3)

Rick James
Rick James

Reputation: 142298

Using LOWER() prevents use of the index. So, switch to a ..._ci collation and ditch the LOWER.

Consider a FULLTEXT index; it is much faster than LIKE%...`. The former is fast; the latter is a full table scan.

OR is almost always a performance killer.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562330

I created a little table:

create table dotdotdot (
  col varchar(20),
  othercol int,
  key(col)
);

I did an EXPLAIN on a query similar to the one you showed:

explain select * from dotdotdot where lower(col) = 'value'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dotdotdot
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

Notice the type: ALL which means it can't use the index on col. By using the lower() function, we spoil the ability for MySQL to use the index, and it has to resort to a table-scan, evaluating the expression for every row. As your table gets larger, this will get more and more expensive.

And it's unnecessary anyway! String comparisons are case-insensitive in the default collations. So unless you deliberately declared your table with a case-sensitive collation or binary collation, it's just as good to skip the lower() function call, so you can use an index.

Example:

explain select * from dotdotdot where col = 'value'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dotdotdot
   partitions: NULL
         type: ref
possible_keys: col
          key: col
      key_len: 23
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

The type: ref indicates the use of a non-unique index.

Also compare to using wildcards for pattern-matching. This also defeats the use of an index, and it has to do a table-scan.

explain select * from dotdotdot where col like '%value%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dotdotdot
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

Using wildcards like this for pattern-matching is terribly inefficient!

Instead, you need to use a fulltext index.

You might like my presentation Full Text Search Throwdown and the video here: https://www.youtube.com/watch?v=-Sa7TvXnQwY

In the other answer you ask if using OR helps. It doesn't.

explain select * from dotdotdot where col like 'value%' or col like '%value%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dotdotdot
   partitions: NULL
         type: ALL
possible_keys: col
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

Notice the optimizer identifies the col index as a possible key, but then ultimately decides not to use it (key: NULL).

Upvotes: 3

Simon
Simon

Reputation: 2423

No, this would not improve the query performance significantly. MySQL will match the WHERE clause "per row" and therefore inspect ALL of the conditions before proceeding to the next row. Hitting the index first may slightly increase the performance if there is a match, but this gain will most likely be overtaken by the double evaluation in case the first condition does not match.

What could have helped is :

1) run the query with like 'Constant%'

2) run another query with like '%Constant%'

in which case, the first one may be accelerated if there is a match. However, you will most likely suffer from the overhead and perform worse in 2 queries than in one.

Moreover, the LIKE operator is case insensitive. Therefore, the lower(column) is unnecessary.

Meanwhile, if you expect your data to match principally on the first condition, and rarely on the second, then YES, this would lead to an increase as the second condition is not evaluated.

Upvotes: 1

Related Questions