Re: Covering Indexes

On Tue, Jul 17, 2012 at 6:08 PM, Simon Riggs <span dir="ltr"><<a href="mailto:simon@2ndquadrant.com"
target="_blank">simon@2ndquadrant.com</a>></span>wrote:<br /><div class="gmail_quote"><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">On 17 July
201216:54, David E. Wheeler <<a href="mailto:david@justatheory.com">david@justatheory.com</a>> wrote:<br /> >
Yeah,but that index is unnecessarily big if one will never use c or d in the search. The nice thing about covering
indexesas described for SQLite 4 and implemented in MSSQL is that you can specify additional columns that just come
alongfor the ride, but are not part of the indexed data:<br /> ><br /> >     CREATE INDEX cover1 ON table1(a,b)
COVERING(c,d);<br/> ><br /> > Yes, you can do that by also indexing c and d as of 9.2, but it might be nice to be
ableto include them in the index as additional row data without actually indexing them.<br /><br /></div>Can you
explainwhat you mean by "without actually indexing them"?<br /> ISTM that it is a non-feature if the index is already
non-unique,and<br /> the difference is simply down to the amount of snake oil applied to<br /> the descriptive text on
therelease notes.<br /></blockquote></div><br />It would be useful in non-unique indexes to store data without ordering
operators(like xml).<br />