> The DB schema is:
> CREATE TABLE xxx(a TEXT, b TEXT, c TEXT);
> CREATE INDEX idx on xxx(b);
>
> The command
> explain select rowid, a, b, c from xxx where b in('qwerty') order by rowid;
> shows 58 opcodes.
>
> The command
> explain select rowid, a, b bbb, c from xxx where bbb in('qwerty') order by
> rowid;
> shows 35 opcodes.
>
> Could somebody explain - is it a bug or a feature? :)
Looks like an optimization oversight. It seems that the index is not used
(i.e., slower) if the
SELECT references an alias of the index column in an IN clause.
This also produces 35 opcodes:
explain select rowid, a, b, c from xxx where +b in('qwerty') order by rowid;
sqlite> explain query plan select rowid, a, b, c from xxx where b in('qwerty')
order by rowid;
0|0|TABLE xxx WITH INDEX idx
sqlite> explain query plan select rowid, a, b bbb, c from xxx where bbb
in('qwerty') order by
rowid;
0|0|TABLE xxx USING PRIMARY KEY ORDER BY
sqlite> explain query plan select rowid, a, b, c from xxx where +b in('qwerty')
order by rowid;
0|0|TABLE xxx USING PRIMARY KEY ORDER BY
____________________________________________________________________________________
Don't get soaked. Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
