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 [link]
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
> 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.