cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Antonio Gallardo" <>
Subject RE: [ESQL] Improvement....
Date Tue, 28 Jan 2003 19:52:01 GMT
Hunsberger, Peter dijo:
> Just sent a blank reply by mistake, sorry about that.
>>> Torsten,
>>> You're chasing a non-existent problem.  There is never a real life
>>> case that will have both good performance for N records and bad
>>> performance for
>>> N+1 records.  The only way you can guarantee having good performance
>>> N+for N
>>> records is if you can build an index.  If you can build an index then
>>>  the search will always terminate after looking at N+1 records.
>> I give you a "real world" case;
>> In natural language;
>> Give me the first COLOR (3D point) which resides not further than dE
>> units
>> from COLOR [L1, a1, b1].
>> In SQL database, I can only place the L, a, b columns, individually,
>> and
> the
>> spherical search would be;
>> WHERE SQRT( SQ( L - L1 ) + SQ( a - a1) + SQ( a - a2 ) ) < dE
>> where L1, a1, b1 and dE are parameters given at the invocation of the
> Select
>> statement.
>> <exclusion>
>> I could go into a long discussion how this could be optimized, both in
>> SQL
>> (multi-levelled subqueries and regeneration of indexes) and internal
>> to
> the
>> DB engine (3D indecies), but I won't.
>> </exclusion>
>> If I have a well populated database, evenly and randomly spread out, I
> will on
>> the average have a 100% penalty on LIMIT N+1, as the search will go
> through
>> roughly the same number of records to find the "next one", which I
>> don't
> care
>> about.
> Niclas,
> First, as you point out this particular case can be optimized. Moreover,
> I believe you could add counting columns or other indexable columns that
> would resolve the issue?
> However, the real issue here is how should you be limiting the query?
> IIRC the LIMIT +1 logic was added fairly recently in order to solve a
> bug that someone else encountered.

Just for the record, I was the one who find the problem and the solution. ;-)

Antonio Gallardo.

> Kludging up this logic to fix a
> different problem doesn't make sense.  In your case the SQL has business
> logic embedded in it and as such it makes sense to completely qualify
> this logic and add the LIMIT clause (or a HAVING) directly to the SQL
> statement.  There can't be a need to dynamically change the limit (?)
> and as such you don't need the ability to sub the limit in from the ESQL
> as a parameter...???

To unsubscribe, e-mail:
For additional commands, email:

View raw message