integer primary key and index table

(1) By sqlite3_preupdate_count (XiongZaiBingGan) on 2021-03-13 07:56:38 [link]

The document says 'integer primary key ' is an alias for the rowid.so if a column set 'integer primary key',does sqlite create a index table to mapping this column and rowid columm? 

select * from table1 where col = 123; if col is integer primary key,does this select statement do a binary search in index-table for rowid first，then use the rowid to do binary search for row data in table1?

(2.2) By Keith Medcalf (kmedcalf) on 2021-03-13 08:24:03 edited from 2.1 in reply to 1

Did you read <https://sqlite.org/rowidtable.html>?

>  if col is integer primary key,does this select statement do a binary search in index-table for rowid first

No.  The rowid is the key to the table row in the b-tree.  In the case you describe above the rowid is used to traverse the table b-tree directly.

Additional indexes contain the "key columns" and the rowid.  The key columns are used to traverse the index b-tree to find the rowid of the row in the underlying table, and then that rowid is used directly to traverse the table b-tree to locate the row data.

See also <https://sqlite.org/fileformat.html>

(3.1) By Keith Medcalf (kmedcalf) on 2021-03-13 08:31:21 edited from 3.0 in reply to 1 [link]

> does sqlite create a index table to mapping this column and rowid columm

No.  The `integer primary key` is the rowid.  If you do not declare a rowid (`integer primary key`) for a rowid table, then one exists anyway, it just does not form part of the persistent data of the table -- it is invisible and subject to change at a whim -- but the rowid may be accessed by the magic name `rowid`, `_rowid_`, or `oid` provided that those names have not been declared elsehow in the rowid table definition.