CODEWITHSUNDEEP
databasesqliteandroid-cursor
AJW
AJW

Reputation: 1649

SQLite: can I reverse the order of row inserts with an AUTOINCREMENT Table?

I have a RecyclerView list of items that uses an SQLite database to store user input data. I use the traditional _id column as INTEGER PRIMARY KEY AUTOINCREMENT. If I understand correctly, newly inserted rows in the database are added below existing rows and the new ROWID takes the largest existing ROWID and increments it by +1. Therefore, a cursor search for the latest insert will have to scan down the entire set of rows to reach the bottom of the database. For example, after 10 inserts, the cursor has to search down from 1, 2, 3,... until it gets to row 10.

To avoid a lengthy search of the entire set of ROWIDs, is there any way to have new inserts be added to the top of the database and not the bottom? That way a cursor search for the latest insert using moveToFirst() will be very fast since the cursor will stop at the first row it searches, the top of the database. The cursor would search 10, 9, 8,...3,2,1 and therefore the search would be very fast since it would stop at 10, the first row at the top of the database.

Upvotes: 0

Views: 614

Answers (2)

MikeT
MikeT

Reputation: 57083

First, if you are concerned about overheads then use the recommended INTEGER PRIMARY KEY as opposed to INTEGER PRIMARY KEY AUTOINCREMENT. Both will result in a unique id, the latter has overheads as per :-

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed. SQLite Autoincrement

If I understand correctly, newly inserted rows in the database are added below existing rows and the new ROWID takes the largest existing ROWID and increments it by +1.

Generally BUT not necessarily, there is no guarantee that the value will increment by 1.

AUTOINCREMENT utilises a table called sqlite_seqeunce that has a single row per table that stores the highest last used sequence number along with the table name. The next sequence number will be that value + probably 1 UNLESS the highest rowid is greater than the value in the sqlite_sequence table.

Without AUTOINCREMENT then the next sequence is the highest rowid + probably 1.

AUTOINCREMENT guarantees a higher number. Without AUOINCREMENT can use a lower number (BUT not until the number would be greater than 9223372036854775807). If AUTOINCREMENT would use a number higher that this then an SQLITE_FULL exception will happen.

Again with regard to rowid's and searching :-

The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that 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. ROWIDs and the INTEGER PRIMARY KEY

To avoid a lengthy search of the entire set of ROWIDs, is there any way to have new inserts be added to the top of the database and not the bottom?

Yes there is, simply specify the value for the rowid or typically the alias when inserting (but beware using an already used value and good luck with managing the numbering). However, I doubt that doing so would result in a faster search. Tables have a rowid by default largely due to the rowid being optimised for searching by rowid.

Upvotes: 0

Sidias-Korrado
Sidias-Korrado

Reputation: 403

You are thinking too much about the database internals. Indexes are designed for this kind of optimisation.

Make a new numeric column where you put your wished ordering as a value and use order by in selects. Do not forget to make an index on this column and verify your selects do use the indexes. (explain)

Upvotes: 2

Related Questions