Keith, this definitely explains the observed time as it is relative to
count(a)*count (ab)**2, thus non-linear.
And a correlated sub-query is generally recalculated for each row.
But I do not agree with everything.
In my example it is correlated to the outermost query, and not to the
Well of course. You are aware that a correlated subquery means "for each
candidate result execute the query"?
So as you have formulated the query it means:
for each row in a
compute the result count which
for each ab candidate row
calculate whether it is the minimum
Also of note is that when you make an index on ab (size), your original query,
unchanged, becomes about 3 times faster than my modification. I'm not sure on
what it looks at to decide if a temporary autoindex is worth it, but in this
case it would have been.
When you move back to your more
I acknowledge you said you weren't so much interested in an alternative
solution, but...
How about something like
select a, min(size) as minSize, recCount
from
(select a, size, count(*) as recCount
from a inner join ab
using (a)
group by a, size)
group by a;
The inner one will group by
4 matches
Mail list logo
