On 3/2/15, James K. Lowden <jklowden at schemamania.org> 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 no index supports the search > criteria. Specifically, the first member of the primary key, "a", is > not mentioned in the WHERE clause.
James is correct. However, SQLite is sometimes able to use an index of the form PRIMARY KEY(a,b) with a WHERE clause of just b=:N. It will use the index to skip forward to each cluster of b=:N values. If there are too many distinct a values, then a full table scan turns out to be faster, so this technique is only used if the number of distinct a values is small. The only way that SQLite will ever know that there are few distinct a values is if you have run ANALYZE on the database. See https://www.sqlite.org/optoverview.html#skipscan for additional information. But that rule does not apply in this case, so James is still correct. -- D. Richard Hipp drh at sqlite.org
