someone else might give a more technical and scientific explanation, but my take is that "SELECT n FROM table" is just that -- a row returned for every row in the table because there is no WHERE clause constraining the results. "SELECT max(....) - 1 FROM table" on the other hand GROUPs the result before returning it, hence GROUPing acts as a constraint. By that logic, "SELECT (SELECT max(rowid) FROM MyTable') - 10" is very fast because it is SELECTing one record from a returned set of one record... basically, the external SELECT is just as superfluous as "SELECT SELECT (SELECT max(rowid) FROM MyTable') - 10" would be just as fast as well, and so on.
On 6/14/07, Guy Hindell <[EMAIL PROTECTED]> wrote:
Ah, OK, I see that doing 'SELECT 1 FROM MyTable' returns a 1 for every row, so I can see where the effort is probably going. However, 'SELECT max(rowid) - 1 FROM MyTable' still only produces one result row (obviously I'm experimenting with a much smaller database now). Still need an explanation rather than just relying on my own speculation. Cheers guy Guy Hindell wrote: > I have a fairly large table (10million rows) with a simple INTEGER > PRIMARY KEY AUTOINCREMENT field. > > Executing 'SELECT max(rowid) FROM MyTable' is very fast, as is > 'SELECT min(rowid) FROM MyTable'. > > However, 'SELECT max(rowid) - min(rowid) FROM MyTable' is slow > (apparently accessing every row). Further, 'SELECT max(rowid) - 1 > FROM MyTable' is slow - in fact using any constant in this expression > (including 0) results in a slow query. > > Finally, 'SELECT (SELECT max(rowid) FROM MyTable') - 10' is very fast. > > I am curious and would be grateful if someone can explain what is > going on here. > > guy > > > ----------------------------------------------------------------------------- > > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
-- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
