2

In SQLite, specifying a column as an INTEGER PRIMARY KEY makes the column an alias for the rowid, meaning that SELECT statements can then be very fast as they can exploit the B-tree structure in which the data in stored.

Is the same level of performance achieved by specifying CREATE UNIQUE INDEX on the column instead? In particular, if we create a UNIQUE INDEX on an INTEGER column, does SQLite then make the column an alias for the rowid, with the corresponding performance advantages.

The way I see it currently, specifying INTEGER PRIMARY KEY will be at least as good as CREATE UNIQUE INDEX, and potentially better.

StewartDouglas
  • 41
  • 2
  • The ROWID column is the ROWID column. The only way to make an alias is with ['INTEGER PRIMARY KEY'](https://www.sqlite.org/autoinc.html); not a unique index. – user2864740 Aug 02 '15 at 04:59
  • Anyway, the ROWID column (which *every* SQLite table has) represents the physical layout of the data. The aliasing effect does not change this. When creating a different primary key (not an alias column) then that is effectively a separate (unique) index. Thus the performance of a non-alias primary key and a unique index ought to be the same while an alias 'INTEGER PRIMARY KEY' should be a wee bit faster as the data is immediately available on a lookup (that is, a query using the primary key covers all columns). – user2864740 Aug 02 '15 at 05:21
  • Thank you @user2864740. I actually believe that the ROWID column can be omitted by specifying WITHOUT ROWID on table creation ([docs](https://www.sqlite.org/withoutrowid.html)) which can introduce storage and speed advantages in some circumstances. – StewartDouglas Aug 03 '15 at 01:29

1 Answers1

1

The documentation says:

retrieving or sorting records by rowid is fast. 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.

and:

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.

There is no other way of making a column an alias for the rowid.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thank you for your reply @CL. So given that INTEGER PRIMARY KEY is the only way to alias the rowid, can we then infer that SELECT statements on tables which use INTEGER PRIMARY KEY will be faster than SELECTing from tables built with an index, _even_ when that index is built on an INTEGER column? – StewartDouglas Aug 03 '15 at 01:19
  • Only in the worst case. Usually, everything is in the cache anyway. – CL. Aug 03 '15 at 08:02