Re: Covering Indexes

On 06/28/2012 02:16 PM, David E. Wheeler wrote:
> Hackers,
>
> Very interesting design document for SQLite 4:
>
>    http://www.sqlite.org/src4/doc/trunk/www/design.wiki
>
> I'm particularly intrigued by "covering indexes". For example:
>
>      CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);
>
> This allows the following query to do an index-only scan:
>
>      SELECT c, d FROM table1 WHERE a=? AND b=?;
>
> Now that we have index-only scans in 9.2, I'm wondering if it would make sense to add covering index support, too, where additional, unindexed columns are stored alongside indexed columns.
>
> And I wonder if it would work well with expressions, too?
>
> David

This is analogous to SQL Server's "include" :

|CREATE NONCLUSTERED INDEX my_idx|
|ON my_table (status)|
|INCLUDE (someColumn, otherColumn)|

Which is useful, but bloats the index.

-- 
Andreas Joseph Krogh<andreak@officenet.no>  - mob: +47 909 56 963
Senior Software Developer / CEO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc