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