CODEWITHSUNDEEP
c#sqlite
Nikki Locke
Nikki Locke

Reputation: 2941

SQLite gives automatic index warnings, even though the key is the first key in an explicit index

I have a table with the following schema:

CREATE TABLE LoanEvents (
    Id integer primary key not null, 
    LoanId integer not null default '0', 
    Period integer not null default '0',
    ... more data
    )

it has an index defined:

CREATE UNIQUE INDEX LoanEvents_LoanId_Period
    on LoanEvents(LoanId,Period)

I am running a complex query with joins on this table, and I am getting the message:

SQLite warning (284): automatic index on LoanEvents(LoanId)

Why do I get this warning, when there is already an index with LoanId as the first key?

Is there anything I can do to get rid of the warning, without adding an unnecessary index?

Upvotes: 0

Views: 872

Answers (2)

Nikki Locke
Nikki Locke

Reputation: 2941

I found the answer - my query had an error in its JOINs - I accidentally joined the LoanId to a string field (with a very similar name to the integer field I meant to join to).

As far as I can see, that meant that SQLite needed a string index on LoanId, rather than the integer index it already had.

The question shown as a possible duplicate, SQLite Database gives warning automatic index on <table_name>(column) After upgrading Android L does actually contain a clue in the second answer, which states that the index has to have the same collation as the column that needs it. I guess we can add that it should have the same type too.

Upvotes: 1

Opiyo Adamsy
Opiyo Adamsy

Reputation: 71

I hope this helps: SQLite Gives Automatic Index Warning

Disclaimer: I haven't personally tested it yet.

Upvotes: 0

Related Questions