When an integer column is marked as a primary key in an SQLite table, should an index be explicitly created for it as well? SQLite does not appear to automatically create an index for a primary key column, but perhaps it indexes it anyway, given its purpose? (I will be searching on that column all the time).
Would the situation be any different for a string primary key?
When using
CREATE TABLE data(a INTEGER PRIMARY KEY, b, ...)
the traditional additional (hidden) column rowid won't be there: the column a itself will be the row id.
Indeed, the doc states:
In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.
and also
if a [...] table has a primary key that consists of a single column and the declared type of that column is "INTEGER" [...], then the column becomes an alias for the rowid.
Since a is the row id, no index is necessary, queries on column a will be fast thanks to the B-tree structure:
The data [...] is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key.
Note: the [...] part I've not quoted is relative to precisions about differences between normal tables and tables with the WITHOUT ROWID clause, but this is totally out of topic here.