As usual your answer is perfect in explanation! Thank you very much. On Jan 24, 2008 2:49 AM, <[EMAIL PROTECTED]> wrote: > "Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > > Hello All, > > I've found that SQLite-3.5.4 doesnt use index in this situation: > > > > sqlite> create table t1 (id int primary key, val int); > > sqlite> create table t2 (id unique, val int primary key); > > sqlite> explain query plan update t1 set val = (select t2.val from t2 > > where t1.id = t2.id); > > 0|0|TABLE t1 > > 0|0|TABLE t2 > > > > In this case, SQLite should takes value from t2 via unique id INDEX, > > but it doesn't > > > > The t2.id field has no datatype specified. That means it has > an affinity of NONE. (See http://www.sqlite.org/datatypes3.html > paragraph 2.1 bullet 3.) That means that if you insert a string > into t2.id it goes in as a string: > > INSERT INTO t2(id) VALUES('123'); > SELECT typeof(id) FROM t2 WHERE rowid=last_insert_rowid(); > --> answer "text" > > Or if you insert an integer, it goes in as an integer: > > INSERT INTO t2(id) VALUES(123); > SELECT typeof(id) FROM t2 WHERE rowid=last_insert_rowid(); > --> answer "integer" > > But the t1.id column to which you are comparing t2.id has > an affinity of INTEGER. (paragraph 2.1 bullet 1.) That means > if you insert a string it is converted into an integer if it > looks like an integer. > > INSERT INTO t1(id) VALUES('123'); > SELECT typeof(id) FROM t1 WHERE rowid=last_insert_rowid(); > --> answer "integer" > > Now, the index on t2(id) also uses NO-affinity because the > affinity of the column is NONE. So the index stores separate > entries in separate places for '123' and 123. But the value > you are comparing against is always an integer, because it is > coming out of t1.id which has integer affinity. So if you > look up the entry using just the integer value 123, you will > miss the '123' entry. That is unacceptable. Hence, you cannot > use a value with INTEGER-affinity as the key to an index > with NO-affinity. > > Hence the index on t2.id cannot be used to speed the search. > > You can get the index to work by saying: > > create table t1(id int primary key, val int); > create table t2(id INT unique, val int primary key); > > Note the added INT in the definition of t2.id, thus > giving it integer affinity. You'll still be able to store > text in t2.id if you want to, but if that text looks like > an integer, it is converted into an integer. > > Please also not that INT PRIMARY KEY is not the same > thing as INTEGER PRIMARY KEY. You probably want > to use INTEGER PRIMARY KEY in this context, not what > you have - but that is a whole other issue. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > >
-- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
