Re: Covering Indexes

<div class="moz-cite-prefix">On 06/28/2012 02:16 PM, David E. Wheeler wrote:<br /></div><blockquote
cite="mid:7138506E-2A5D-4AA6-A8CD-DC9FB4D7344E@justatheory.com"type="cite"><pre wrap="">Hackers,
 

Very interesting design document for SQLite 4:
 <a class="moz-txt-link-freetext"
href="http://www.sqlite.org/src4/doc/trunk/www/design.wiki">http://www.sqlite.org/src4/doc/trunk/www/design.wiki</a>

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,
whereadditional, unindexed columns are stored alongside indexed columns.
 

And I wonder if it would work well with expressions, too?

David
</pre></blockquote><br /> This is analogous to SQL Server's "include" :<br /><br /><div class="container" title="Hint:
double-clickto select code"><div class="line number1 index0 alt2"><code class="java plain">CREATE NONCLUSTERED INDEX
my_idx</code></div><divclass="line number2 index1 alt1"><code class="java plain">ON my_table (status)</code></div><div
class="linenumber3 index2 alt2"><code class="java plain">INCLUDE (someColumn, otherColumn)</code></div></div><br />
Whichis useful, but bloats the index.<br /><pre class="moz-signature" cols="72">-- 
 
Andreas Joseph Krogh<a class="moz-txt-link-rfc2396E"
href="mailto:andreak@officenet.no"><andreak@officenet.no></a> - mob: +47 909 56 963
 
Senior Software Developer / CEO - OfficeNet AS - <a class="moz-txt-link-freetext"
href="http://www.officenet.no">http://www.officenet.no</a>
Public key: <a class="moz-txt-link-freetext"
href="http://home.officenet.no/~andreak/public_key.asc">http://home.officenet.no/~andreak/public_key.asc</a>
</pre>