On Thu, Aug 30, 2012 at 4:02 AM, Navaneeth.K.N <navaneet...@gmail.com>wrote:
> 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!
>
Which one runs faster on your system with actual data?
>
> Also, is there way to get rid of temporary B-TREE for order by?
>
Changing the UNIQUE constraint on the WORDS table to be
UNIQUE(word,confidence) will probably do the trick.
>
> --
> Thanks
> Navaneeth
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
