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
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
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
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
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
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
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:
>
>
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
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
9 matches
Mail list logo