Hello,
Can somebody tell me why this (simple) query take so much time? This
query does nothing more than querying a table and JOINing two other
tables together.
SELECT
ti1.topicID AS topicID,
ti2.topic_textID AS parent,
n.level,
n.level_order
FROM navigation AS n
LEFT JOIN topic_ids AS ti1 ON ti1.topicID = n.topicID
LEFT JOIN topic_ids AS ti2 ON ti2.topicID = n.parent_topicID
WHERE ti1.topic_textID = 'XXXXX';
I thought I optimized the table good with indexes but one such a query
takes 500 to 1000ms in my C++ program.
Here are my table definitions and the indexes (unfortunately I need the
VARCHAR(20) field because I get the "topicID" only as text:
CREATE TABLE topic_ids(
topicID INTEGER,
topic_textID VARCHAR(20),
PRIMARY KEY(topicID)
);
CREATE INDEX topic_textID ON topic_ids(topic_textID);
CREATE TABLE navigation(
topicID INTEGER PRIMARY KEY,
parent_topicID INTEGER,
level VARCHAR(20),
level_order INTEGER
);
CREATE INDEX parent_topicID ON navigation(parent_topicID);
CREATE INDEX level ON navigation(level);
CREATE INDEX level_order ON navigation(level_order);
I need to execute this query in a database application each time a new
page is opened. So 500ms are really too much. A few ms would be great.
And the tables itself are not really huge:
SELECT COUNT(*) FROM navigation;
19469
SELECT COUNT(*) FROM topic_ids;
19469
Does anybody have an idea what's going wrong here? How can I speed up
this query?
Thank you very much in advance,
Luke
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
