[Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?
charlie at egenix.com
Tue Dec 6 07:28:45 EST 2005
On 2005-12-06 at 10:48:29 [+0100], Chris Withers <chris at simplistix.co.uk>
> Charlie Clark wrote:
> >>>>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...
> Huh? the original poster was talking about MySQL, why should he care
> about any other rdb?
Well, he was talking about using ZSQL and cited by MySQL and MS SQL
statements. ie., he was interested in a generic approach. At least that's
how I read it.
> >>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.
> How would you implement batching in a way that's agnostic of the rdb used?
Currently I do this with a script but the LCD solution would be to use a
LIMITs based on parameters passed in. Essentially, however, the RDBMS has
to support the feature and the Python DB API should be cover it. Having had
to work with both MySQL and SQLite recently the compromises required to
support "lesser" systems can be really annoying.
> > Result sets aren't supported directly
> by what?
By ZopeDA's and ZSQL
> > 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().
I've not discovered this to be a real problem yet although my result sets
are rarely more than several thousand items long when ZPTs rendering speed
becomes the problem. One could imagine RDBMs support in Zope3 being so much
better if only anyone could be really bothered about it ;-)
More information about the Zope-DB