Re: [sqlite] SQLite omit using index
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]
-
Re: [sqlite] SQLite omit using index
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]
-
