indexing - Is an index needed for a primary key in SQLite?

Question

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?

Answer 1

It does it for you.

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.