CODEWITHSUNDEEP
sqlsqlite
PoorLuzer
PoorLuzer

Reputation: 25066

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

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?

Upvotes: 1

Views: 1646

Answers (3)

Nick Dandoulakis
Nick Dandoulakis

Reputation: 43110

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

Upvotes: 2

Chris McCall
Chris McCall

Reputation: 10397

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

Upvotes: 1

Richard Simões
Richard Simões

Reputation: 12791

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.

Upvotes: 4

Related Questions