Understanding SQLite Query Planning - Stack Overflow

admin管理员组

文章数量:1391974

I went through the following article on query planning in SQLite. However, I do not understand the following, stated in Sec. 1.2:

One technique for avoiding a full table scan is to do lookups by rowid (or by the equivalent INTEGER PRIMARY KEY). To lookup the price of peaches, one would query for the entry with a rowid of 4:

SELECT price FROM fruitsforsale WHERE rowid=4;

Since the information is stored in the table in rowid order, SQLite can find the correct row using a binary search.

Is that really true - is the information in the table sorted by the rowid (primary key) by default?

I went through the following article on query planning in SQLite. However, I do not understand the following, stated in Sec. 1.2:

One technique for avoiding a full table scan is to do lookups by rowid (or by the equivalent INTEGER PRIMARY KEY). To lookup the price of peaches, one would query for the entry with a rowid of 4:

SELECT price FROM fruitsforsale WHERE rowid=4;

Since the information is stored in the table in rowid order, SQLite can find the correct row using a binary search.

Is that really true - is the information in the table sorted by the rowid (primary key) by default?

• sqlite

Share Improve this question Follow asked Mar 13 at 14:55 ImahnImahn 55622 gold badges77 silver badges2626 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Yes, this is true, see https://www.sqlite./lang_createtable.html#rowid:

The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value.

本文标签： Understanding SQLite Query PlanningStack Overflow