Performance of LIKE 'xyz%' v/s LIKE '%xyz'

Question

I was wondering how the LIKE operator actually work. Does it simply start from first character of the string and try matching pattern, one character moving to the right? Or does it look at the placement of the %, i.e. if it finds the % to be the first character of the pattern, does it start from the right most character and starts matching, moving one character to the left on each successful match?

Not that I have any use case in my mind right now, just curious.

edit: made question narrow

Answer 1

If there is an index on the column, putting constant characters in the front will lead your dbms to use a more efficient searching/seeking algorithm. But even at the simplest form, the dbms has to test characters. If it is able to find it doesn't match early on, it can discard it and move onto the next test.

Answer 2

Usually the main performance bottleneck is IO. The efficiency of the LIKE operator can be only important if your whole table fits in the memory otherwise IO will take most of the time.

AFAIK oracle can use indexes for prefix matching. (like 'abc%'), but these index cannot be used for more complex expressions. Anyway if you have only this kind of queries you should consider using a simple index on the related column. (Probably this is true for other RDBMS's as well.)

Otherwise LIKE operator is generally slow, but most of the RDBMS have some kind of full text searching solution. I think the main reason of the slowness is that LIKE is too general. Usually full text indexes has lots of different options which can tell the database what you really want to search for, and with these additional information the DB can do its task in a more efficient way.

As a rule of thumb I think if you want to search in a text field and you think performance can be an issue, you should consider your RDBMS's full text searching solution, or the real goal is not text searching, but this is some kind of "design side effect", for example xml/json/statuses stored in a field as text, then probably you should consider choosing a more efficient data storing option. (if there is any...)

Answer 3

The LIKE search condition uses wildcards to search for patterns within a string. For example:

WHERE name LIKE 'Mickey%'

will locate all values that begin with 'Mickey' optionally followed by any number of characters. The % is not case sensitive and not accent sensitive and you can use multiple %, for example

WHERE name LIKE '%mouse%'

will return all values with 'mouse' (or 'Mouse' or 'mousé') in it.

The % is inclusive, meaning that

WHERE name like '%A%'

will return all that starts with an 'A', contain 'A' or end with 'A'.

You can use _ (underscore) for any character on a single position:

WHERE name LIKE '_at%'

will give you all values with 'a' as the second letter and 't' as the third. The first letter can be anything. For example: 'Batman'

In T-SQL, if you use [] you can find values in a range.

WHERE name LIKE '[c-f]%'

it will find any value beginning with letter between c and f, inclusive. Meaning it will return any value that start with c, d, e or f. This [] is T-SQL only. Use [^ ] to find values not in a range.

Finding all values that contain a number:

WHERE name LIKE '%[0-9]%'

returns everything that has a number in it. Example: 'Godfather2'

If you are looking for all values with the 3rd position to be a '-' (dash) use two underscores:

WHERE NAME '__-%'

It will return for example: 'Lo-Res'

Finding the values with names ends in 'xyz' use:

WHERE name LIKE '%xyz'

returns anything that ends with 'xyz'

Finding a % sign in a name use brackets:

WHERE name LIKE '%[%]%'

will return for example: 'Top%Movies'

Searching for [ use brackets around it:

WHERE name LIKE '%[[]%'

gives results as: 'New York [NY]'

The database collation's sort order determines both case sensitivety and the sort order for the range of characters. You can optionally use COLLATE to specify collation sort order used by the LIKE operator.