Hi Michael
I tried your example and have experienced the same results. Then I
created the following two indices, now the two queries are same fast:
CN_execute "CREATE INDEX idx_t1_id ON t1 (id)"
CN_execute "CREATE INDEX idx_t2_id ON t2 (id)"
It seems that sqlite doesn't create an index for primary keys that are
not "INTEGER" :-) But haven't verified it yet.
khamis
Michael Gross wrote:
Khamis Abuelkomboz wrote:
CREATE INDEX idx_t2_t1id ON t2 (t1id, deleted);
Doent not help.
whereas the following quere takes "no" time:
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE
t1.deleted<>1
This is fast, because no entries has been found to be joined.
This is not true - "deleted" hast the value 0 in all rows - so
"t1.deleted=0" and "t1.deleted<>1" brings the same result.
And now - check this out - the following query ist fast too:
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE NOT NOT
(t1.deleted=0)
Whereas
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE (t1.deleted=0)
is slow. And as far as I undestand this should be the same, or do I miss
sth?
thx
Michael
--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
* C/C++
* Java
* .NET (including CSharp, VB.Net and other .NET components)
* Classic Visual Basic
* PHP, HTML, XML, ASP, CSS
* Tcl/Tk,
* Perl
* Python
* SQL,
* m4 Preprocessor
* Cobol
