sqliteunique-indexcovering-index

sqlite: unique covering index

Let's say I have a table t1 with three columns A, B, C, where (A,B) comprise a unique key (with hundreds of thousands of rows). Since 90% of queries will be of the form SELECT C FROM t1 WHERE A=? and B =?, I suppose I want to have a covering index for A, B, and C.

How do I have a covering index that includes C and defines (A,B) as unique?

I'm thinking of having two indexes: one unique and one covering and using INDEXED BY to force the covering index for SELECTS.

Is this reasonable?

Solution

  • In SQLite 3.8.2 or later, you can make this table a WITHOUT ROWID table, so that the table itself behaves like a covering index:

    CREATE TABLE t1 (
    A,
    B,
    C,
    PRIMARY KEY (A, B)
) WITHOUT ROWID;