On 2017/07/14 8:57 PM, David Raymond wrote:
"The only way to get that information from an index that includes all values of y
(even and odd) is to scan."
-Yes, but scan the index, not scan the raw table.
Actually, scanning the table is faster,
One common misconception I find in the wild is that an Index is a gift
from heaven and ALWAYS better to use. There is nothing "Magical" about
an Index or its speed. It comes at a price, a high one at that, paid
both on INSERT and on using it for look-ups - and the price is paid only
to gain the feature of possibly saving many many cycles by jumping an
ordered chain based on ordinality and good cardinality that otherwise
would have required a sequential traversal. Whenever any QP doubts the
advantage that will be offered by ordinal jumping of an Index, it is the
wise choice to rather traverse the table directly.
Why you ask?
Well, scanning the table is faster than scanning the Index since the
table scan and printing results more-or-less involves (in pseudo code):
ROW_ID = 0
While ROW_ID < TableLength do
IF check Row.Field THEN Print Row.FieldValues
Row.Next
Inc(ROW_ID)
End_While
Where scanning the Index involves:
IDX_ID = 0
While IDX_ID < IndexLength do
IF check Index[IDX_ID].Value THEN
ROW_ID = Index[IDX_ID].RowID
Row.Navigate_To(ROW_ID) // This is the timeous step
Print Row.FieldValues
End_IF
index.Next
Inc(IDX_ID)
End_While
You could use an index on y for any deterministic function on just y. As you're
scanning the index on y you compute f(y): (y & 2 != 0) for the first time you
see a specific y, then since you now know f(y) you can take or skip all consecutive
records with the same y since they're nicely all together in the index.
Indeed. If the OP were to make an Index like CREATE INDEX ON blah WHERE
y & 2 != 0; Then, and only then, can the Index be used in a query such
as ... WHERE y & 2 != 0. The functions must match.
As Scott alluded to - The OP's attempt to query "WHERE y & ? != 0" from
an Index on Y is the same as having an Index on Y and querying "...
WHERE y * broomstick + ? = 74" - There is no way that result can be
gleaned from the Indexed values without recalculating it, which renders
it useless as a look-up. If In stead he tried to query the
mathematically equivalent (solving for Y) "... WHERE y = (74 - ?) /
broomstick; then it would work because the result is the actual indexed
value.
There is however no way the statement " Y & ? != 0 " can be solved for
Y, so the Index cannot be made useful.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
