SQLite always does a Full Table Scan when like used?

I have a text column containing URLs that are uniqe.

I need to do a SQL pattern match query, say using the 'like' SQL o开发者_如何学运维perator.

In such a scenario, will SQLite do a FTS ( Full Table Scan ) regardless of whether I have an index on that column or not ( column is a primary key )?

It seems to be that its doing a FTS as the speed of operation is telling - or is the speed impact due to the 'like' query although the column is indexed?

The LIKE operator will perform a full table scan if operand string begins with a wildcard character ("LIKE '%foo'"). It will use an index (if available) otherwise.

The SQLite Query Planner

4.0 The LIKE optimization

Terms that are composed of the LIKE or GLOB operator can sometimes be used to constrain indices. There are many conditions on this use:

1. The left-hand side of the LIKE or GLOB operator must be the name of an indexed column.
2. The right-hand side of the LIKE or GLOB must be a string literal that does not begin with a wildcard character. [...]

Update: by default case_sensitive_like mode is OFF. Turning it ON might make it behave like =.

PRAGMA case_sensitive_like = ON; -- OFF
SELECT * FROM your_table WHERE field LIKE '...'

If your LIKE uses a wildcard at the beginning of the match expression, such as '%hris McCall', indexes will not be used.