how to turn off SQLite autoindex feature?

Question

My Android application uses SQLite and ORMLite to work with data. There is need to insert a big amount of data at one time and I want to make this faster than it is for this moment. For that I want to drop indexes then insert data and then recreate indexes.

Problem: is when application creates database - SQLite adds indexes to some fields in db automatically. They called sqlite_autoindex_%TableName%%ColumnName%_1

my ORM entites have fields marked to be indexed in db. And after creating tables ORMLite creates that indexes. SQLIte auto indexes dublicates indexes, created by ORMLIte so I need to configure SQLite to turn autoindexes off. I have found that this feature turns off with query "PRAGMA automatic_index = false;" So i overrided method of OrmLiteSqliteOpenHelper:

@Override
public SQLiteDatabase getWritableDatabase() {
    SQLiteDatabase db = super.getWritableDatabase();
    db.execSQL("PRAGMA automatic_index = false;");
    return db;
}

That didn't help. Wrong place for query? Please help

Answer 1

Unlike what's near to always stated, you can go without getting automatic indexes generated, which may significantly reduce a DB size (after own experience).

After my own experience, you got sqlite_autoindex_"table_name"_N when you have tables with "ROWID", which is the default. If you design a table using WITHOUT ROWID [synax], there is no more automatic index (along to no more "ROWID") for that table.

However, note the following [source]:

• WITHOUT ROWID tables, can't use AUTOINCREMENT;
• WITHOUT ROWID tables, must have a PRIMARY KEY (and for this, your best bet is to use a prefix of the table rows);
• WITHOUT ROWID tables behave differently with regards to PK values: there use to be a bug in SQLite, letting it accept NULL values fro primary keys (it was hence diverging from the standard). Later, when WITHOUT ROWID was added, it was decided that it will also enforce the standard semantic which disallows NULL for primary keys.

Additionally, after my own experience, if there is any UNIQUE constraint which does not have the PK as a prefix, the sqlite_autoindex_"table_name"_N will still be generated (one have to imagine what an implementation could be, to guess why it is so).

As a side note to help avoid confusion with another use of the term “automatic index” with SQLite, note this [source] :

Do not confuse automatic indexes with the internal indexes (having names like "sqlite_autoindex_table_N") that are sometimes created to implement a PRIMARY KEY constraint or UNIQUE constraint. The automatic indexes described here exist only for the duration of a single query, are never persisted to disk, and are only visible to a single database connection. Internal indexes are part of the implementation of PRIMARY KEY and UNIQUE constraints, are long-lasting and persisted to disk, and are visible to all database connections. The term "autoindex" appears in the names of internal indexes for legacy reasons and does not indicate that internal indexes and automatic indexes are related.

Formally, with SQLite, automatic index, really means something else. This confusion does not help, one must have it in mind when reading the official documentation.

Answer 2

SQLite automatically creates indexes for column in PRIMARY KEY or UNIQUE constraints. (The only exception is INTEGER PRIMARY KEY.) These cannot be disabled.