SQL ‘like’ vs ‘=’ performance

by

See https://web.archive.org/web/20150209022016/http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx

Quote from there:

the rules for index usage with LIKE
are loosely like this:

  • If your filter criteria uses equals =
    and the field is indexed, then most
    likely it will use an INDEX/CLUSTERED
    INDEX SEEK

  • If your filter criteria uses LIKE,
    with no wildcards (like if you had a
    parameter in a web report that COULD
    have a % but you instead use the full
    string), it is about as likely as #1
    to use the index. The increased cost
    is almost nothing.

  • If your filter criteria uses LIKE, but
    with a wildcard at the beginning (as
    in Name0 LIKE ‘%UTER’) it’s much less
    likely to use the index, but it still
    may at least perform an INDEX SCAN on
    a full or partial range of the index.

  • HOWEVER, if your filter criteria uses
    LIKE, but starts with a STRING FIRST
    and has wildcards somewhere AFTER that
    (as in Name0 LIKE ‘COMP%ER’), then SQL
    may just use an INDEX SEEK to quickly
    find rows that have the same first
    starting characters, and then look
    through those rows for an exact match.

(Also keep in mind, the SQL engine
still might not use an index the way
you’re expecting, depending on what
else is going on in your query and
what tables you’re joining to. The
SQL engine reserves the right to
rewrite your query a little to get the
data in a way that it thinks is most
efficient and that may include an
INDEX SCAN instead of an INDEX SEEK)

More Related Contents:

  1. How do I obtain a Query Execution Plan in SQL Server?
  2. Count(*) vs Count(1) – SQL Server
  3. select * vs select column
  4. Inner join vs Where
  5. INNER JOIN vs LEFT JOIN performance in SQL Server
  6. Equals(=) vs. LIKE
  7. SQL JOIN vs IN performance?
  8. Is a view faster than a simple query?
  9. Multiple INSERT statements vs. single INSERT with multiple VALUES
  10. Why would an IN condition be slower than “=” in sql?
  11. Does the order of where clauses matter in SQL?
  12. SQL Joins Vs SQL Subqueries (Performance)?
  13. Normalize array subscripts so they start with 1
  14. Does Foreign Key improve query performance?
  15. Performance of inner join compared to cross join
  16. Spark sql queries vs dataframe functions
  17. How to count occurrences of a column value efficiently in SQL?
  18. How does table alias names affect performance?
  19. Performance issue in using SELECT *? [duplicate]
  20. Favourite performance tuning tricks [closed]
  21. Can an INNER JOIN offer better performance than EXISTS
  22. SQL Server 2008 paging methods?
  23. IN vs. JOIN with large rowsets
  24. Two single-column indexes vs one two-column index in MySQL?
  25. How do you interpret a query’s explain plan?
  26. Which SQL statement is faster? (HAVING vs. WHERE…)
  27. “SELECT *” vs “SELECT ” for performance
  28. Challenge,how to implement an algorithm for six degree of separation?
  29. Oracle , != , ^= operators
  30. sql query joins multiple tables – too slow (8 tables)

Leave a Comment