On Mon, 2 Mar 2015 09:48:28 +0000
Jan Asselman <jan.asselman at iba-benelux.com> wrote:
> "CREATE TABLE IF NOT EXISTS test
> (
> a INTEGER,
> b INTEGER,
> c INTEGER,
> d BLOB,
> PRIMARY KEY (a, b DESC)
> );"
...
> "SELECT * FROM test WHERE b < ? AND c > ?;"
...
> A full table scan is executed because column a is not part of the
> query.
A full table scan is executed because no index supports the search
criteria. Specifically, the first member of the primary key, "a", is
not mentioned in the WHERE clause. (Primary keys are often implemented
as indexes, as they are in SQLite. In general, though, a primary key is
just a logical constraint.) The table is the only source of
information sufficient to answer the query.
It's actually not sufficient for column "a" to be "mentioned"; it has
to be searchable. For example,
WHERE a between b and c
also requires a table scan because the information in the index is
still insufficient.
--jkl
