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

Charlie Clark 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> 
wrote:
> 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.

Charlie


More information about the Zope-DB mailing list