0

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?

Nikki Locke
  • 2,759
  • 6
  • 29
  • 53
  • 1
    Show the query? Also read up on how to interpret EXPLAIN QUERY PLAN output. – Shawn Oct 01 '18 at 18:27
  • Possible duplicate of [SQLite Database gives warning automatic index on (column) After upgrading Android L](https://stackoverflow.com/questions/27373344/sqlite-database-gives-warning-automatic-index-on-table-namecolumn-after-upgr)
     – Dour High Arch Oct 01 '18 at 18:29
  • Plus, having default column values with a unique index is kind of strange. If you tried making that index after populating the table, are you sure it was successful? – Shawn Oct 01 '18 at 18:30
  • @DourHighArch Not really a duplicate, since OP is already making an index on the relevant column. – Shawn Oct 01 '18 at 18:37
  • FYI, it is not a duplicate, as I already have a suitable index. Yes, the index exists (I copied the create statement from the schema in the actual database). The query is far to complex (involving about 8 tables) and confidential to post here. If no-one knows the answer off the cuff, I will try to create a non-confidential version and post it. – Nikki Locke Oct 02 '18 at 13:47

2 Answers2

1

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.

Nikki Locke
  • 2,759
  • 6
  • 29
  • 53
0

I hope this helps: SQLite Gives Automatic Index Warning

Disclaimer: I haven't personally tested it yet.

Opiyo Adamsy
  • 61
  • 7
  • Considering that post is about **not** having an index on a column and OP's post clearly shows he's making an index on the column in question... you're making the same mistake the guy who flagged this post as a duplicate of that one did. – Shawn Oct 01 '18 at 18:39