Richard Hipp <drh-czdrofg0bjidnm+yrof...@public.gmane.org> writes: > It would be convenient to be able to decode the keys. But it is not > possible to do so, in general.
This, then, takes my inquiry to the topic of covering indices. On the "The Design of SQLite4" Wiki page¹, it says (emphasis mine): ,----[ Section 3.6 Explicit Cover Index, paragraph 2 ] | The COVERING clause makes the values of c and d available in the index, | which means that they can be /extracted directly from the index/ without | a second lookup. `---- and ,----[ Section 3.6 Explicit Cover Index, paragraph 3 ] | The COVERING ALL clause means that all columns of the table are | /duplicated in the index/ which guarantees that the original table will | never have to be consulted to complete a query that uses the index. `---- The Wiki page "Query Planning" contains a similarly abstract description: ,----[ Query Planning, Section 1.7 Covering Indices ] | This new index contains all the columns of the original FruitsForSale | table that are used by the query - both the search terms and the | output. We call this a "covering index". Because all of the information | needed is in the covering index, SQLite never needs to consult the | original table in order to find the price. `---- A (too) literal reading of this would suggest that the index is the key-encoded concatenation of -- to use the example from that first Wiki page -- four values for columns a, b, c, and d. However, if one were to query for c and d while only constraining a and b, then the values for columns c and d would need to be extracted from the encoded key. If, as you say, SQLite cannot (or will not) extract values from an encoded key, then does the covering index really just encode column values a and b as the key, and store values for c and d in a record corresponding to the key? > Many keys can be decoded, but for TEXT keys with application-defined > collating sequences, the encoding is not reversible. Many applications > will use the ucol_getSortkey() interface to ICU to get the TEXT keys, > and there is no way to reverse that encoding to recover the original > text. I take it that in this case, the key encoding is not bijective; rather, the encoding is a surjective function, collapsing multiple source values into the same encoded value. Footnotes: ¹ http://www.sqlite.org/src4/doc/trunk/www/design.wiki -- Steven E. Harris _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
