On 3/2/15, Jan Asselman <jan.asselman at iba-benelux.com> wrote: > Hi, > > I created the following table in sqlite 3.8.6 > > "CREATE TABLE IF NOT EXISTS test > ( > a INTEGER, > b INTEGER, > c INTEGER, > d BLOB, > PRIMARY KEY (a, b DESC) > );" > > When I execute the following query > > "SELECT * FROM test WHERE b < ? AND c > ?;" > > A full table scan is executed because column a is not part of the query. > > But when I step over the rows they are not returned in primary key sort > order. Why is this?
The PRIMARY KEY is really just a UNIQUE constraint unless (1) it refers to a single column of type INTEGER or (2) the table is a WITHOUT ROWID table. Simplest solution for you seems to be to add the keywords "WITHOUT ROWID" after the ")" and before the ";". > > If I look at the images at the query planning document > (https://www.sqlite.org/queryplanner.html) I get the idea that the primary > key B-tree should be used to traverse the table when a full table scan is > executed. And since the 'DESC' keyword is used on column b in the primary > key, I would expect that, as the rowId increases, the values retuned for > column b would decrease. But this is not the case. > > Does a full table scan then ignore the PK B-tree? Does it perhaps scan all > table pages in the order in which they appear in the file system? I guess > this would make sense to increase traversal speed, since it needs to check > all pages anyway? > > If I explicitly order using an 'ORDER BY' statement then - looking at the > query plan - sqlite seems to perform a full table scan and store the result > in a temporary table which is then sorted. I'd like to avoid the memory > consumption produced by this query plan... > > Any help or information would be appreciated! > Thanks in advance! > > Jan Asselman > > > > > > > > iba AG: Supervisory Board: Horst Anhaus, chairman; Management Board: Dr. > Ulrich Lettau CEO, Marta Anhaus, Harald Opel; Registered Office: > Fuerth/Germany; Registration Office: Fuerth, HRB 9865, Vat.Reg.No DE > 132760166, WEEE-Reg.No. DE11469996 > > This message is intended only for the named recipient and may contain > confidential or privileged information. Taking notice of this message by > third parties is not permitted. If you have received it in error, please > advise the sender by return e-mail and delete this message and any > attachments. Any unauthorized use or dissemination of this information is > strictly prohibited. > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
