"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] -----------------------------------------------------------------------------
