Michael Gross wrote:
Hello
I use sqlite 3.2.2. I have a strange performance problem. I am able to
solve the problem by a slight change in the query but I want to ask if
somebody can explain this behavior to me:
I have two tables:
CREATE TABLE t1 (id VARCHAR(40) NOT NULL PRIMARY KEY, deleted BIT);
CREATE TABLE t2 (id VARCHAR(40) NOT NULL PRIMARY KEY, t1id
VARCHAR(40), deleted BIT);
CREATE INDEX idx_t1_deleted ON t1 (deleted);
CREATE INDEX idx_t2_deleted ON t2 (deleted);
CREATE INDEX idx_t2_t1id ON t2 (t1id);
try the following index
CREATE INDEX idx_t2_t1id ON t2 (t1id, deleted);
When I now insert 2500 times (%d is the incrementing variable):
INSERT INTO t1 (id, deleted) VALUES ('%d', 0);
INSERT INTO t2 (id, t1id, deleted) VALUES ('%d', '%d', 0);
INSERT INTO t2 (id, t1id, deleted) VALUES ('xxx%d', '%d', 0);
The the following query takes about 1 minute:
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE t1.deleted=0
This is slow, because the join is not indexed
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.
Keep in mind, that only one index can be used for a query or subquery.
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
