[Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?
charlie at egenix.com
Fri Dec 2 05:49:18 EST 2005
On 2005-12-02 at 10:33:34 [+0100], Chris Withers <chris at simplistix.co.uk>
> Charlie Clark wrote:
> >>That doesn't work if you use offsets and limits to only return part of a
> >>result set...
> > 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?
ah, that might well be in the realms of db-tuning and magic but one might
expect a count() operation to be slightly faster as it requires less data to
be packaged. The implementation of LIMITs and result sets might vary
significantly from system to system.
> > Of course, ZopeDAs such as our mxODBCZopeDA can be extended to support
> > additional commands on the connection but that is not part of the ZSQL
> > specification and will vary from RDBMS to RDBMS as the initial mail noted.
> Well, that's why MySQL does it as an SQL statement, no? ;-)
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".
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). Of course, it can be a right Tony Blair rewriting a
complex SQL query just to add a count in there as opposed to calling a method
on a cursor object. And this is where ZopeDA connections could do with
improved support beyond the current "it's good enough as it is" situation so
that database functions can be called directly more easily where required.
But as we all know, so far no one has come forward to champion new generation
ZSQLs and ZopeDAs to do this.
More information about the Zope-DB