Which performances better in a query, 'LIKE' or '='?

I am using asp.开发者_JAVA百科net mvc3.

Which of the two '=' or 'LIKE' will perform faster in my SQL query.

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

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

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)

1. Equal is fastest
2. Then LIKE 'something%'
3. LIKE '%something' is slowest.

The last one have to go through the entire column to find a match. Hence it's the slowest one.

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

1. It is clearer what the expression of the query is
2. In this case the search term does not contain any characters of particular significance to the LIKE operator but if you wanted to search for bar = '10% off' you would need to escape these characters when using LIKE.
3. 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.