I am using asp.net mvc3.
Which of the two '='
or 'LIKE'
will perform faster in my SQL query.

- 74,508
- 24
- 191
- 319

- 17
- 3
-
If your query is slow, changing from LIKE to `=` is probably not the biggest improvement you can make. Post your query as well, and people can suggest other changes. – RB. Sep 26 '11 at 06:17
-
possible duplicate of [Equals(=) vs. LIKE](http://stackoverflow.com/questions/543580/equals-vs-like) – onedaywhen Sep 26 '11 at 07:06
5 Answers
=
will be faster than LIKE
because testing against exact matches is faster in SQL. The LIKE
expression needs to scan the string and search for occurrences of the given expression for each row. Of course that's not a reason not to use LIKE
. Databases are pretty optimized and unless you discover that this is a performance bottleneck for your application you should not be afraid of using it. Doing premature optimizations like this is not good.

- 1,023,142
- 271
- 3,287
- 2,928
-
+1 for good recommend: `Doing premature optimizations like this is not good.` – Saeed Amiri Sep 26 '11 at 06:33
-
2-1 `LIKE` without a leading wildcard can use an index seek and `LIKE` without any wildcards at all is basically an equality seek with slightly different semantics about trailing space. As the OP is talking about using them interchangeably it would be reasonable to conclude they are not using wildcards. – Martin Smith Sep 26 '11 at 09:55
As Darin says, searching for equality is likely to be faster - it allows better use of indexes etc. It partly depends on the kind of LIKE operation - leading substring LIKE queries (WHERE Name LIKE 'Fred%'
) can be optimized in the database with cunning indexes (you'd need to check whether any special work is needed to enable that in your database). Trailing substring matches could potentially be optimized in the same sort of way, but I don't know whether most databases handle this. Arbitrary matches (e.g. WHERE Name LIKE '%Fred%Bill%'
) would be very hard to optimize.
However, you should really be driven by functionality - do you need pattern-based matching, or exact equality? Given that they don't do the same thing, which results do you want? If you have a LIKE
pattern which doesn't specify any wildcards, I would hope that the query optimizer could notice that and give you the appropriate optimization anyway - although you'd want to test that.
If you're wondering whether or not to include pattern-matching functionality, you'll need to work out whether your users are happy to have that for occasional "power searches" at the cost of speed - without knowing much about your use case, it's hard to say...

- 1,421,763
- 867
- 9,128
- 9,194
Equal and like are different operators so are not comparable
- Equal is exact match
- LIKE is pattern matching
That said, LIKE without wildcards should run the same as equal. But you wouldn't run that.
And it depends on indexes. Every row will need examined without an index for any operator.
Note: LIKE '%something'
can never be optimised by an index (edit: see comments)

- 422,506
- 82
- 585
- 676
-
1There's no reason why a database *shouldn't* be able to optimize for `LIKE '%something'` using a trie starting at the tail of the string instead of the head. Whether that's implemented by many databases is a different matter. – Jon Skeet Sep 26 '11 at 06:22
-
@Jon Skeet: A computed, index column with REVERSE would do it too easily enough. But most RDBMS has full text searching – gbn Sep 26 '11 at 06:24
-
Right. I don't know exactly how full text searching works in most databases - if it's based on tokenization then it will help in some cases but not others, of course. Fundamentally it's the first part of your answer that I like: they're different operators. – Jon Skeet Sep 26 '11 at 06:26
-
1`LIKE` without wildcards does have slightly different behaviour at least in SQL Server. Not sure if this is standard `SELECT 1 WHERE 'foo' LIKE 'foo '; SELECT 1 WHERE 'foo' = 'foo '` – Martin Smith Sep 26 '11 at 09:51
- Equal is fastest
- Then
LIKE 'something%'
LIKE '%something'
is slowest.
The last one have to go through the entire column to find a match. Hence it's the slowest one.

- 99,844
- 45
- 235
- 372
As you are talking about using them interchangeably I assume the desired semantics are equality.
i.e. You are talking about queries such as
WHERE bar = 'foo'
vs WHERE bar LIKE 'foo'
This contains no wild cards so the queries are semantically equivalent. However you should probably use the former as
- It is clearer what the expression of the query is
- In this case the search term does not contain any characters of particular significance to the
LIKE
operator but if you wanted to search forbar = '10% off'
you would need to escape these characters when usingLIKE
. - Trailing space is significant in
LIKE
queries but not=
(tested on SQL Server and MySQL not sure what the standard says here)
You don't specify RDBMS, in the case of SQL Server just to discuss a few possible scenarios.
1. The bar
column is not indexed.
In that case both queries will involve a full scan of all rows. There might be some minor difference in CPU time because of the different semantics around how trailing space should be treated.
2. The bar
column has a non unique index.
In that case the =
query will seek into the index where bar = 'foo'
and then follow the index along until it finds the first row where bar <> 'foo'
. The LIKE
query will seek into the index where bar >= 'foo'
following the index along until it finds the first row where bar > 'foo'
3. The bar
column has a unique index.
In that case the =
query will seek into the index where bar = 'foo'
and return that row if it exists and not scan any more. The LIKE
query will still do the range seek on Start: bar >= 'foo' End: bar <= 'foo'
so will still examine the next row.

- 1
- 1

- 438,706
- 87
- 741
- 845