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"       "[email protected]"
    "15"      "[email protected]"
    "17"      "[email protected]"
    "19"      "[email protected]"
    "23"      "[email protected]"
    ...

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 
    ); 

Answer 1

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