SQLite: Autoincrement and insert or ignore will produce unused Autoincrement Keys

Question

I'm using a table Mail with auto-increment Id and Mail Address. The table is used in 4 other tables and it is mainly used to save storage (String is only saved once and not 4 times). I'm using INSERT OR IGNORE to just blindly add the mail addresses to the table and if it exists ignore the update. This approach is MUCH faster than checking the existence with SELECT ... and do an INSERT if needed.

For every INSERT OR IGNORE the auto-increment, no matter if ignored or done the auto-increment Id is incremented. I one run I have approx. 500k data sets to proceed. So after every run the the last auto-increment key is incremented by 500k. I know there are 2^63-1 possible keys, so a long time to use them all up.

I also tried INSERT OR REPLACE, but this will increment the Id of the dataset on every run of the command, so this is not a solution at all.

Is there a way to prevent this increase of auto-increment key on every INSERT OR IGNORE?

Table Mail Example (replaced with pseudo Addresses)

    mIdMail   mMail
    "1"       ""
    "7"       "mail1@example.com"
    "15"      "mail2@example.com"
    "17"      "mail3@example.com"
    "19"      "mail4@example.com"
    "23"      "mail5@example.com"
    ...

Insert Query (Using Java Lib: org.apache.commons.dbutils)

    INSERT OR IGNORE 
    INTO MAIL 
    ( mMail  ) 
    VALUES ( ? );

Table Definition

    CREATE TABLE IF NOT EXISTS MAIL (
       mIdMail          INTEGER PRIMARY KEY AUTOINCREMENT, 
       mMail            CHAR(90) UNIQUE 
    );

This doesn't answer your question, but I am gathering you don't do a not exists for speed, but I assume the internals would do the same check, so is it faster? If it is indeed faster, one option maybe to reset the next increment to the max+1 at the end of the batch, this may save a few wasted. Otherwise just cop it, it doesn't really matter that they are wasted, but I am gathering you are worried you may run out after your dead :) — Ab Bennett, Nov 05 '17 at 11:31
@AbBennett It is tremendously faster. I didn't measure in this project, but in other with Mysql/Mariadb. It is one command that is run effectively by the SQLite engine and not two commands. I'm running multi threaded, so I would have to implement some locking between reading the result and inserting, which slows down the program too. I wrote an answer once about MySQL/MariaDB: https://stackoverflow.com/a/34360548/5330578 — notes-jj, Nov 05 '17 at 11:53
Cool. I have never used this syntax before, so good to learn. One thing I had to do is split a load into two tables and I had one with a negative increment, and then wrap a view over the two tables. Pretty ordinary solution but works — Ab Bennett, Nov 05 '17 at 11:56

score 6 · Accepted Answer · answered Nov 06 '17 at 10:41

6

To get autoincrementing values without gaps, drop the AUTOINCREMENT keyword. (Yes, you get autoincrementing values even without it.)

answered Nov 06 '17 at 10:41

CL.

173,858
17
217
259

Excellent. I didn't find the article before. And it seams to be faster too. – notes-jj Nov 06 '17 at 14:02

score 2 · Answer 2 · answered Nov 05 '17 at 12:55

Auto-increment keys behave the way they do specifically because the database guarantees their behavior -- regardless of concurrent transactions and transaction failures.

Auto-increment keys have two guarantees:

They are increasing, so later inserts have larger values than earlier ones.
They are guaranteed to be unique.

The mechanism for allocating the keys does not guarantee no gaps. Why not? Because no-gaps would incur a lot more overhead on the database. Basically, each transaction on the table would need to be completely serialized (that is completed and committed) before the next one can take place. Generally, that is a really bad idea from a performance perspective.

Unfortunately, SQLite doesn't have the simplest solution, which is simply to call row_number() on the auto-incremented keys. You could try to implement a gapless auto-increment using triggers, significantly slowing down your application.

My real suggestion is simply to live with the gaps. Accept them. Surrender. That is how the built-in method works, and for good reason. Now design the rest of the database/application keeping this in mind.

score 0 · Answer 3 · answered Jan 31 '23 at 09:50

0

I had the same issue, and changing "INSERT OR IGNORE" into "INSERT OR FAIL" solved the problem, so now when it fails the id value doesn't increment.

answered Jan 31 '23 at 09:50

Pinonirvana

920
1
8
12

SQLite: Autoincrement and insert or ignore will produce unused Autoincrement Keys

3 Answers3