Primary Key Effect on Performance in SQLite

Question

Hi,

I have an sqlite database used to store information about backup jobs. Each run, it increases approximately 25mb as a result of adding around 32,000 entries to a particular table.

This table is a "map table" used to link certain info to records in another table... and it has a primary key (autoincrement int) that I don't use.

sqlite will reserve 1, 2, 4, or 8 bytes for INT column depending on its value. This table only has 3 additional columns, also of INT type.

I've added indexes to the database on the columns that I use as filters (WHERE) in my queries.

In the presence of indexes, etc. and in the situation described, do primary keys have any useful benefit in terms of performance?

Note: Performance is very, very important to this project - but not if 10ms saved on a 32,000 entry job means an additional 10MB of data!

Answer 1

A primary key index is used to look up a row for a given primary key. It is also used to ensure that the primary key values are unique.

If you search your data using other columns, the primary key index will not be used, and as such will yield no performance benefit. Its mere existance should not have a negative performance impact, either, though.

An unneccessary index wastes disk space, and makes updates slower. It should have no negative impact on query performance.

Answer 2

If you really don't use this id what don't you drop this column + primary key? The only reason to keep a non-used primary key id column alive is to make it possible to create a master-detail relation with another table.

Another possibility is to keep the column but to drop the primary key. That will mean that the application has to take care of providing a unique id with every insert statement. Before and after each batch operation you have to check whether this column is still unique. This doesn't work in for instance MySQL and Oracle because of multi concurrency issues but it does work in sqlite.