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. 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]
