Re: [sqlite] Could someone explain why this query is so slow
Ahh I was sure that this was being created :( ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Could someone explain why this query is so slow
On 9/21/2011 12:19 PM, Paul Sanderson wrote: Thanks Igor That makes sense but if I drop MD5 from the query (the vast majority of MD5 values would be null anyway) and use select ID FROM rtable WHERE search> 0 and isf = 0 ORDER BY afo The result from explain query plan is 0|0|0 SCAN TABLE rtable (~3 rows) 0|0|0 USE TEMP B-TREE FOR ORDER BY Which seems to indicate that the b-tree is still being created (I'll test shortly, but running another long test at the moment) Double-check that you indeed have an index on afo. Show the output of this statement: select * from sqlite_master where tbl_name='rtable'; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Could someone explain why this query is so slow
Thanks Igor That makes sense but if I drop MD5 from the query (the vast majority of MD5 values would be null anyway) and use select ID FROM rtable WHERE search > 0 and isf = 0 ORDER BY afo The result from explain query plan is 0|0|0 SCAN TABLE rtable (~3 rows) >> 0|0|0 USE TEMP B-TREE FOR ORDER BY Which seems to indicate that the b-tree is still being created (I'll test shortly, but running another long test at the moment) On 21 September 2011 14:33, Igor Tandetnik wrote: > Paul Sanderson wrote: >> select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 ORDER BY >> afo >> >> explain query plan gives the following for the initial query >> 0|0|0 SEARCH TABLE rtable USING INDEX md5_a (md5=?) (~2 rows) >> 0|0|0 USE TEMP B-TREE FOR ORDER BY >> >> it seems that the extra time is taken creating a b-tree for the order >> by but if correct why is the existing index not used? > > Only one index per table can be used. Imagine you have two lists of (the > same) people, one sorted by first name and one by last name. You need to list > all Peters alphabetically by their last name. You can either use the first > list to find all Peters, then sort them by hand. Or you can use the second > list to enumerate everyone in the order of last name, and select only Peters. > But you can't use both lists. > >> is there anyway of speeding this up? > > A single index on (md5, afo) may help. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Paul Sanderson Sanderson Forensics +44 (0)1326 572786 www.sandersonforensics.com http://www.twitter.com/sandersonforens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Could someone explain why this query is so slow
Paul Sanderson wrote: > select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 ORDER BY > afo > > explain query plan gives the following for the initial query > 0|0|0 SEARCH TABLE rtable USING INDEX md5_a (md5=?) (~2 rows) > 0|0|0 USE TEMP B-TREE FOR ORDER BY > > it seems that the extra time is taken creating a b-tree for the order > by but if correct why is the existing index not used? Only one index per table can be used. Imagine you have two lists of (the same) people, one sorted by first name and one by last name. You need to list all Peters alphabetically by their last name. You can either use the first list to find all Peters, then sort them by hand. Or you can use the second list to enumerate everyone in the order of last name, and select only Peters. But you can't use both lists. > is there anyway of speeding this up? A single index on (md5, afo) may help. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Could someone explain why this query is so slow
The query below takes about 10 mins to run, any idea why this would be? select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 ORDER BY afo The same query without the ORDER BY takes a few seconds. select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 There are approximately 800K rows in the table and all columns are indexed explain query plan gives the following for the initial query 0|0|0 SEARCH TABLE rtable USING INDEX md5_a (md5=?) (~2 rows) 0|0|0 USE TEMP B-TREE FOR ORDER BY without the ORDER BY I get just 0|0|0 SEARCH TABLE rtable USING INDEX md5_a (md5=?) (~2 rows) it seems that the extra time is taken creating a b-tree for the order by but if correct why is the existing index not used? is there anyway of speeding this up? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
