On Jan 7, 2009, at 6:11 PM, Lukas Haase wrote:
> 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';
SQLite should be running this query in O(NlogN).
If you change the first LEFT JOIN to a plain old JOIN (which should
give equivalent results by virtue of the WHERE clause restricting
ti1.topic_textID to not be NULL) then it should run in O(logN) - much
faster. Try it and let me know.
SELECT
ti1.topicID AS topicID,
ti2.topic_textID AS parent,
n.level,
n.level_order
FROM navigation AS n
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
D. Richard Hipp
d...@hwaci.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
