Key-only selects (was Re: Help! my install etc)

In article <1992Apr10.141813.26235@cpqhou.compaq.com> thomasr@cpqhou.compaq.com (Thomas Rush) writes: > In article <1992Mar26.204539.7416@informix.com> cortesi@informix.com writes: > > > >Regarding your choice of query, "where description matches '*MEM*'" > >will always use a sequential scan. MATCHES (or LIKE) with a leading > >wildcard can't use an index. > > > While it may be true to say that MATCHES with a leading > wildcard _doesn't_ use an index, isn't it possible that using the > index might be faster: > > since the index will have only this column, amount of data > to be searched is only the column we're interested in,... I am informed that the OnLine engine uses this idea, calling it a "key-only select." When all columns of a table that are referenced in a query are contained in an index, the index is read instead of the table. By using the index as a sort of "instant projection" of the table, the engine cuts down on the amount of data transferred. The use of a key-only select would be reflected in the optimizer output of SET EXPLAIN ON. This suggests a possible indexing strategy for high-use tables: to include redundant columns in indexes so as to encourage key-only selects. Suppose you have a large, high-usage table with a column that is often used for joins, K. You observe that a common use of the table is to join on K in order to pick up the value of column D1 or column D2. You could try making indexes on (K, D1) and on (K, D2) in order to speed these queries. Of course indexes cost space to store and time to build as well as time to maintain under updates, so this is not an automatic win! > and, > it is possible that finding a hit may allow inteligent use > of Informix's compression scheme to guarantee that the next > _n_ rows are hits also? This idea is not used by our engines. I don't know why, tho I can imagine some engineering value to keeping the algorithm for key storage untied from the key selection algorithm.