Dear list members,
I was surprised to find out that indices are not used in expressions of
the form 'column is value' in contrast to 'column=value' or 'column is
null'. Compare the following example:
create table test (id integer primary key, val double);
insert into test (val) values(random());
insert into test (val) values(1);
[repeat a number of times to get more values]
create index index_test on test (val);
The following two query plans make use of the index:
explain query plan select id from test where val=1;
explain query plan select id from test where val is NULL;
This one leads to a complete table scan:
explain query plan select id from test where val is 1;
although the result of the statement is the same as with '='.
I find this annoying because I use 'is' in a prepared query where I do
not know in advance in which case a value in the where clause is null.
So I have the choice of either running an inefficient query or making my
code more complicated by examining the data for NULL values and changing
'=' to 'is' in the query only where neccessary. Is there any reason for
this behaviour? Is there any chance this might be changed in the future?
Best regards,
Andreas
--
Andreas Borg
Medizinische Informatik
UNIVERSITÄTSMEDIZIN
der Johannes Gutenberg-Universität
Institut für Medizinische Biometrie, Epidemiologie und Informatik
Obere Zahlbacher Straße 69, 55131 Mainz
www.imbei.uni-mainz.de
Telefon +49 (0) 6131 175062
E-Mail: [email protected]
Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren
Sie bitte sofort den
Absender und löschen Sie diese Mail. Das unerlaubte Kopieren sowie die
unbefugte Weitergabe
dieser Mail und der darin enthaltenen Informationen ist nicht gestattet.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
