Hello,
I have two tables named "patterns_content" and "words".
CREATE TABLE patterns_content (pattern text, word_id integer, primary
key(pattern, word_id))
CREATE TABLE words (id integer primary key, word text unique, confidence
integer default 1, learned integer default 1, learned_on date)
Given a pattern, "abc", I need to get the word for it. For this, I use,
select word, confidence from words as w, (SELECT distinct(word_id) as
word_id FROM patterns_content as pc where pc.pattern = lower('abc') limit
5) as patterns where w.id = patterns.word_id and w.learned = 1 order by
confidence desc
I could also use,
select word, confidence from words where rowid in
(SELECT distinct(word_id) FROM patterns_content as pc where pc.pattern =
lower('abc') limit 5) and learned = 1 order by confidence desc
Both these queries are fast. The only difference between them is the place
where subquery is used. In first one subquery is used as part of the from
clause and second one uses as part of where clause.
When looking throgh the execution plan, they both uses different plans.
Plan for 1st query
--------------
SEARCH TABLE patterns_content AS pc USING COVERING INDEX
sqlite_autoindex_patterns_content_1 (pattern=?) (~2 rows)
SCAN SUBQUERY 1 AS patterns (~2 rows)
SEARCH TABLE words AS w USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
USE TEMP B-TREE FOR ORDER BY
Plan for 2nd query
-------------
SEARCH TABLE words USING INTEGER PRIMARY KEY (rowid=?) (~2 rows)
EXECUTE LIST SUBQUERY 1
SEARCH TABLE patterns_content AS pc USING COVERING INDEX
sqlite_autoindex_patterns_content_1 (pattern=?) (~2 rows)
USE TEMP B-TREE FOR ORDER BY
First one uses a temporary table to store the subquery results. I am
wondering which query to choose. Any help would be great!
Also, is there way to get rid of temporary B-TREE for order by?
--
Thanks
Navaneeth
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
