Re: [sqlite] Slow JOIN on ROWID

On Fri, Oct 14, 2011 at 12:46 PM, Fabian wrote: > 2011/10/14 Simon Slavin > > > > So, OP, try issuing these commands > > > > DROP TABLE sqlite_stat1; > > DROP TABLE sqlite_stat2; > > > > then see whether anything improves. > > > > > I already tried

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 11:23 PM, Simon Slavin wrote: On 14 Oct 2011, at 5:12pm, Fabian wrote: Is this the expected output? If so, ANALYZE was to blame. The query containing 'ORDER BY rowid DESC' is still slower than the one which doesn't specify any order, but the results are closer to eachother now.

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Simon Slavin > > So that should never happen, right ? ANALYZE is meant to make things > faster, not slower. So is that an actual fixable bug or is it one of those > extremely unlikely situations that is hard to fix ? > > So, OP, try issuing these commands > >

Re: [sqlite] Slow JOIN on ROWID

On 14 Oct 2011, at 5:12pm, Fabian wrote: > Is this the expected output? If so, ANALYZE was to blame. The query > containing 'ORDER BY rowid DESC' is still slower than the one which doesn't > specify any order, but the results are closer to eachother now. So that should never happen, right ?

Re: [sqlite] Slow JOIN on ROWID

> > Do you have an sqlite_stat1 table in the database (created by > running ANALYZE)? What is the output of the shell command > How about the contents of the "sqlite_stat1" table? What does > the shell command ".dump sqlite_stat1" show? > > This is the output with a fresh database, where ANALYZE

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Dan Kennedy > > Your EXPLAIN output shows that it is doing a linear scan of > table1. Which is different from what I get here with the same > schema and query. When I run them here, both queries (with and > without the "ORDER BY rowid") use the same query plan.

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 10:13 PM, Fabian wrote: 2011/10/14 Dan Kennedy Good question. Can you enter the following commands into the shell tool and post the complete output (no "QUERY PLAN" this time): .version .schema .explain EXPLAIN SELECT * FROM table1 WHERE data=10

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Dan Kennedy > > Good question. Can you enter the following commands into > the shell tool and post the complete output (no "QUERY PLAN" > this time): > > .version > .schema > .explain > > EXPLAIN SELECT * FROM table1 WHERE data=10 ORDER BY rowid LIMIT 250

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 09:34 PM, Fabian wrote: 2011/10/14 Dan Kennedy If SQLite cannot use an index to for an ORDER BY in a SELECT query and has to do an external sort, the EXPLAIN QUERY PLAN output will have something like this in it: 0|0|0|USE TEMP B-TREE FOR ORDER BY

Re: [sqlite] Slow JOIN on ROWID

On 14 Oct 2011, at 2:59pm, Dan Kennedy wrote: > On 10/14/2011 07:40 PM, Simon Slavin wrote: >> >> I'm sorry, I completely missed the 'data = 10' earlier. If you have an >> index on the data column then that's the index SQLite would used for that >> query. Once it has picked that index it no

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Dan Kennedy > > If SQLite cannot use an index to for an ORDER BY in a SELECT > query and has to do an external sort, the EXPLAIN QUERY PLAN > output will have something like this in it: > > 0|0|0|USE TEMP B-TREE FOR ORDER BY > > Since my EXPLAIN does not show

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 07:40 PM, Simon Slavin wrote: On 14 Oct 2011, at 1:36pm, David Bicking wrote: On 10/14/2011 06:39 AM, Fabian wrote: When I execute: SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 It's very fast, but it's get much slower (10 times) when I add an ORDER BY clause,

Re: [sqlite] Slow JOIN on ROWID

On Oct 14, 2011, at 3:37 PM, Fabian wrote: > 2011/10/14 Petite Abeille > >> >> Hurray! Now you must have the finest query ever to grace the intraweb! A >> true work of beauty :)) >> >> > Here in Belgium we have a saying: Who doesnt honor the petite, is not worth >

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Petite Abeille > > Hurray! Now you must have the finest query ever to grace the intraweb! A > true work of beauty :)) > > Here in Belgium we have a saying: Who doesnt honor the petite, is not worth the big ;) ___

Re: [sqlite] Slow JOIN on ROWID

On Oct 14, 2011, at 2:49 PM, Fabian wrote: > That explains everything! Hurray! Now you must have the finest query ever to grace the intraweb! A true work of beauty :)) ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Slow JOIN on ROWID

On 14 Oct 2011, at 1:49pm, Fabian wrote: > 2011/10/14 Simon Slavin > >> If you have an index on the data column then that's the index SQLite would >> used for that query. Once it has picked that index it no longer has access >> to the rowid index. >> >> CREATE INDEX tdr

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Simon Slavin > > If you have an index on the data column then that's the index SQLite would > used for that query. Once it has picked that index it no longer has access > to the rowid index. > > CREATE INDEX tdr ON table (data, rowid) > > Thanks! That explains

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Petite Abeille > > Much? Really? I get the broadly same execution time for either variant: > > 0|0|0|SCAN TABLE mail_header USING INTEGER PRIMARY KEY (~2192503 rows) > > 0|0|0|SCAN TABLE mail_header (~2192503 rows) > > I get SELECT mail_header.rowid FROM

Re: [sqlite] Slow JOIN on ROWID

On 14 Oct 2011, at 1:36pm, David Bicking wrote: > On 10/14/2011 06:39 AM, Fabian wrote: >> When I execute: >> >> SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 >> >> It's very fast, but it's get much slower (10 times) when I add an ORDER BY >> clause, like rowid ASC or rowid DESC.

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 06:39 AM, Fabian wrote: Exactly. I still don't have optimal performance in the query (although it's much better now), and it seems to be related to ORDER BY. When I execute: SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 It's very fast, but it's get much slower

Re: [sqlite] Slow JOIN on ROWID

On Oct 14, 2011, at 12:39 PM, Fabian wrote: > I still don't have optimal performance in the query (although it's much > better now), and it seems to be related to ORDER BY. Yes, order by has a cost. > When I execute: > > SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 > > It's

Re: [sqlite] Slow JOIN on ROWID

On 14 Oct 2011, at 11:39am, Fabian wrote: > I still don't have optimal performance in the query (although it's much > better now), and it seems to be related to ORDER BY. > > When I execute: > > SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 > > It's very fast, but it's get much

Re: [sqlite] Slow JOIN on ROWID

> > > Exactly. > > I still don't have optimal performance in the query (although it's much better now), and it seems to be related to ORDER BY. When I execute: SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 It's very fast, but it's get much slower (10 times) when I add an ORDER BY

Re: [sqlite] Slow JOIN on ROWID

On Oct 13, 2011, at 10:57 PM, Fabian wrote: > Thank you very much! This approach solved the problem. However, in my > situation I need to select a lot more columns than just 'id' from > 'mail_header', Feel free to select all the relevant columns from the inner query. > and when I look at the

Re: [sqlite] Slow JOIN on ROWID

2011/10/12 Petite Abeille > > Now the join is performed only 250 times, adding just a small overhead > compare the the bare bone query without the join. > > The short of it: minimize the amount of work upfront :) > > Thank you very much! This approach solved the

Re: [sqlite] Slow JOIN on ROWID

On Oct 12, 2011, at 5:16 PM, Fabian wrote: > Why is this very fast (20 ms): > 0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows) > And this very slow (3500ms): > 0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows) > 0 1 1 SCAN TABLE table2 VIRTUAL TABLE INDEX 1:

[sqlite] Slow JOIN on ROWID

Why is this very fast (20 ms): SELECT table1.data1, table1.data2 FROM table1 WHERE table1.data1 = 10 ORDER BY table1.rowid DESC LIMIT 250 OFFSET 50 0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows) And this very slow (3500ms): SELECT