CODEWITHSUNDEEP
androidsqlite
Grisgram
Grisgram

Reputation: 3243

Android SQLite Index on Table

I have a table in a SIP app that stores the call history for all your accounts. I am not a friend of multi-column primary keys, so I put an auto-increment column as my PK.

The first columns of the table are 

    CREATE TABLE IF NOT EXISTS CALLHISTORY
    (
        CALLHISTORYID           INTEGER  PRIMARY KEY AUTOINCREMENT,
        ACCOUNTID               INTEGER  NOT NULL,
        CALLID                  TEXT     NOT NULL,
        ... + many more columns

I get a callId from the CallManager (SIP Server), which is unique for an account (so accountId + callId together build a unique pair).

I set up indices like this:

CREATE INDEX IF NOT EXISTS IX_CALLHISTORY_ACCOUNTID ON CALLHISTORY (ACCOUNTID);
CREATE UNIQUE INDEX UIX_CALLHISTORY_ACCOUNTID_CALLID ON CALLHISTORY (ACCOUNTID,CALLID);

I have several queries on this table in the app, some querying only the accountId, some querying the pair (depends on Activity). Do I really need both indices or will queries that have only the accountId in the where clause use the unique index too?

Thanks for your help!

Upvotes: 2

Views: 2161

Answers (1)

csenga
csenga

Reputation: 4114

From the doc (1.6 Multi-Column Indices):

A multi-column index follows the same pattern as a single-column index; the indexed columns are added in front of the rowid. The only difference is that now multiple columns are added. The left-most column is the primary key used for ordering the rows in the index. The second column is used to break ties in the left-most column. If there were a third column, it would be used to break ties for the first two columns. And so forth for all columns in the index. Because rowid is guaranteed to be unique, every row of the index will be unique even if all of the content columns for two rows are the same.

As a result, the index of the ACCOUNTID and CALLID fields already handles the ordering of the ACCOUNTID so creating index on ACCOUNTID is unnecessary in this case.

Upvotes: 4

Related Questions