[Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?

[Charlie Clark]

On 2005-12-05 at 08:42:09 [+0100], Chris Withers <chris at simplistix.co.uk> 
wrote:
> Charlie Clark wrote:
> >>>SELECT count(attribute) FROM relatiin WHERE condition
> >>
> >>Ah, okay, now I gotcha...
> >>
> >>>only returns 1 result so it's independent of LIMIT and result sets.
> >>
> >>I wonder how the two methods compare efficiency-wise?
> 
> Yes, well, we're talking about MySQL specifically ;-)

Well, with reference to Zope we shouldn't be...

> > Maybe although I sometimes why MySQL does anything: the documentation 
> > seems
> > to imply that many decisions were sort of "let's do it like this": cf.
> > particularly the recommendations for writing queries with JOINs. 
> > Personally I
> > do not see it as valid SQL to predicate a query on "what was just asked".
> 
> Sorry, not a lot of this paragraph made sense :-S

It still does to me. Have you had your morning tea? It's not that important.
 
> > Regarding the original question: if I know I am working with LIMITs or 
> > result
> > sets then this implies I don't want to know the total size of the results
> > beyound len(results).
> 
> Not so. Think of batches:
> 
> "Now showing Results 5 - 10 of 25"
> 
> But why not just return all the rows and only show 5 of them?
> 
> "Now showing Results 5 - 10 of 250000"

Yes, let's talk about batches - ZSQL doesn't implement batching so it 
collects the whole set of results available so len() is available. I agree 
that this is likely to be inefficient for large results but it doesn't 
require any additional calls. Or you do as Dieter suggested an run two calls 
- first one simply counts and the second one effectively does batching. 
Result sets aren't supported directly so unless the DA provides a method to 
query on the connection or cursor you will also have to run a separate query 
with count().

Which means: it would be nice if ZSQL supported batching, preferably by 
result sets and this means the DAs have to expose more of the underlying 
functionality and getting more information from the Python driver.

Charlie