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?
INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY constraints are implemented by creating an index in the database (in the same way as a "CREATE UNIQUE INDEX" statement would). Such an index is used like any other index in the database to optimize queries. As a result, there often no advantage (but significant overhead) in creating an index on a set of columns that are already collectively subject to a UNIQUE or PRIMARY KEY constraint.
If an column is marked INTEGER PRIMARY KEY, it's actually around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. This is because:
...all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table ... 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.
With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid.
Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.
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.
