On 11/17/16, Mark Wagner <[email protected]> wrote: > > Here's my sample code. Note that in the case when the columns are both > integer an index is used and when one is integer and one is not specified > no index is used. > > BEGIN TRANSACTION; > CREATE TABLE seq (value integer); > CREATE TABLE bar (value integer, unique(value)); > CREATE TABLE bar1 (value, unique(value)); > COMMIT; > sqlite> > sqlite> explain query plan SELECT * FROM seq LEFT OUTER JOIN bar1 ON > seq.value = bar1.value; > 0|0|0|SCAN TABLE seq > 0|1|1|SCAN TABLE bar1
This is so because of https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison The first bullet in the paragraph above is the culprit. If a TEXT value that looks like an integer is stored in bar1.value, then it has to be converted into an integer before the comparison. But that conversion potentially changes the order (ex: "10" comes before "9") which means that the index cannot be used for the lookup. -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
