Previous Topic Next topic Print topic


LIKE Predicate

When constructing queries containing a LIKE predicate that involves an indexed column, try to avoid pattern matching strings that begin with percent (%) or underbar (_). If the string in a LIKE predicate begins with either of these two characters, it automatically indicates that a sequential search must be performed on the entire index. For example, given an index on LNAME, the query:

SELECT * FROM employee 
    WHERE lname LIKE "_risp%"

might take significantly longer than the query:

SELECT * FROM employee
    WHERE lname LIKE "Krisp%"
      OR lname LIKE "Crisp%"

In the first case, the entire index must be searched for any names that have "risp" as the second through fifth letter. This is virtually the same as having no index at all. In the second case, the search is for two specific starting strings; these are more quickly found because the search does not have to go through the entire index.

Previous Topic Next topic Print topic