Re: [sqlite] SQLite 3.6.21 - slow query

2010-01-04 Thread Igor Tandetnik
Jeremy Zeiber wrote: > SELECT headerid, > (SELECT COUNT(data) FROM detail AS outerdetail WHERE > headerid=header.headerid AND data NOT IN (SELECT DISTINCT data FROM > detail WHERE headerid FROM header GROUP BY headerid; Try this: select

Re: [sqlite] SQLite 3.6.21 - slow query

2010-01-04 Thread Simon Slavin
On 4 Jan 2010, at 3:44pm, Jeremy Zeiber wrote: > Believe it or not, these indexes made the query run the fastest: > CREATE UNIQUE INDEX idx1 ON detail(headerid,data); > CREATE UNIQUE INDEX idx2 ON detail(data,headerid); That makes perfect sense since your SELECT command does require sorting by

Re: [sqlite] SQLite 3.6.21 - slow query

2010-01-04 Thread Jeremy Zeiber
Simon Slavin wrote: > On 4 Jan 2010, at 3:02pm, Simon Davies wrote: > > >> 2010/1/4 Simon Slavin : >> >>> On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: >>> >>> SELECT headerid, (SELECT (SELECT COUNT(DISTINCT data) FROM detail WHERE

Re: [sqlite] SQLite 3.6.21 - slow query

2010-01-04 Thread Jeremy Zeiber
Simon Davies wrote: > 2010/1/4 Simon Slavin : > >> On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: >> >> >>> That particular query runs in ~ 30 seconds with outerdetail.header or >>> header.headerid. I do have another query which gives the same result >>> that doesn't

Re: [sqlite] SQLite 3.6.21 - slow query

2010-01-04 Thread Simon Slavin
On 4 Jan 2010, at 3:02pm, Simon Davies wrote: > 2010/1/4 Simon Slavin : >> >> On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: >> >>> SELECT headerid, >>> (SELECT (SELECT COUNT(DISTINCT data) FROM detail WHERE >>> headerid=header.headerid)-COUNT(DISTINCT one.data) FROM

Re: [sqlite] SQLite 3.6.21 - slow query

2010-01-04 Thread Simon Davies
2010/1/4 Simon Slavin : > > On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: > >> That particular query runs in ~ 30 seconds with outerdetail.header or >> header.headerid.  I do have another query which gives the same result >> that doesn't quite run as fast as the first, but

Re: [sqlite] SQLite 3.6.21 - slow query

2010-01-04 Thread Simon Slavin
On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: > That particular query runs in ~ 30 seconds with outerdetail.header or > header.headerid. I do have another query which gives the same result > that doesn't quite run as fast as the first, but it is certainly faster > than the second: > >

Re: [sqlite] SQLite 3.6.21 - slow query

2010-01-04 Thread Jeremy Zeiber
Igor Tandetnik wrote: > Jeremy Zeiber wrote: > >> This query runs in ~ 17 ms: >> SELECT COUNT(data) FROM detail AS outerdetail WHERE headerid=4 AND data >> NOT IN (SELECT DISTINCT data FROM detail WHERE headerid<4) >> > > Here the subquery is not coordinated. It is run once, the results

Re: [sqlite] SQLite 3.6.21 - slow query

2010-01-03 Thread Igor Tandetnik
Jeremy Zeiber wrote: > This query runs in ~ 17 ms: > SELECT COUNT(data) FROM detail AS outerdetail WHERE headerid=4 AND data > NOT IN (SELECT DISTINCT data FROM detail WHERE headerid<4) Here the subquery is not coordinated. It is run once, the results are stored in an ephemeral table, then the