Hello to everyone,
This is my first post in the list....
I've got the following 3 tables:
CREATE TABLE A
(
int1 INTEGER,
txt1 TEXT,
int2 INTEGER,
txt2 TEXT,
PRIMARY KEY
(
txt1
)
);
CREATE TABLE B
(
txt1 TEXT,
int1 INTEGER
);
CREATE TABLE C
(
txt1 TEXT,
int1 INTEGER
);
Each table contains 1000 rows. The following query takes about 7 minutes
to return results without using any index apart from the table A's
primary key:
select count(*) from A INNER JOIN Bon (A.txt1 = B.txt1) INNER JOIN C on
(b.txt1 = C.txt1);
If I index columns A.txt1 kai B.txt1, the time gets reduced to milliseconds.
My question is: shouldn't sqlite's engine figure out what I am trying to
do and sort the tables on the fly in an effort to optimize the query?
When using no indexes, a .explain reveals 3 nested loops which take a
long time to return results.
Any help/ideas will be much appreciated.
Thanks for your time.
Kind Regards,
Stergios Zissakis (aka Sterge)
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------