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: [EMAIL PROTECTED] For additional commands, email: [EMAIL PROTECTED]
