integer primary key and index table
(1) By sqlite3_preupdate_count (XiongZaiBingGan) on 2021-03-13 07:56:38 [link] [source]
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] [source]
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 [source]
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.