Is an index needed for a primary key in SQLite?
SqliteIndexingPrimary KeySqlite Problem Overview
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?
Sqlite Solutions
Solution 1 - Sqlite
> 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.
Solution 2 - Sqlite
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.
Solution 3 - Sqlite
A database will always silently create an index for a unique primary key so it can internally check it is unique efficiently.
Having created it, it will use it when necessary.
It won't, of course, always be clustered, and you specify usually in the schema if you want it to be.
Solution 4 - Sqlite
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.