Hello All,
I have a simple query that seems to be going very very very slow. I am not
sure why sqlite is behaving this way, which is why I decided to burden you
guys with my question. My guess is that the query optimizer might not be
able to decipher what exactly I want to do, and thus computes some
inefficient query.
Here is my query:
SELECT * FROM tableR r
INNER JOIN tableU u ON u.id = r.u_id
LEFT JOIN tableP p ON u.p_id = p.id
ORDER BY p.p_name;
CREATE TABLE tableR (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
u_id INTEGER NOT NULL
);
CREATE TABLE tableU (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
p_id INTEGER NOT NULL
);
CREATE TABLE tableP (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
p_name INTEGER NOT NULL
);
CREATE INDEX p_name_index ON tableP (p_name);
CREATE INDEX p_id_index ON tableU (p_id);
CREATE INDEX u_id_index ON tableR (u_id);
tableR has about 5 million records.
tableU has 100k records.
table P has 1k records.
It's very weird. If I change the last join to an INNER JOIN, everything
works great. Any information on how I could improve this query would
greatly help. I know it can't be THIS slow..
Thanks,
J
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
