Re: [sqlite] performance question: SELECT max(rowid) - 1
Trey Mack 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. > Check out http://www.sqlite.org/php2004/slides-all.html Page 61 SELECT max(rowid) FROM MyTable and SELECT min(rowid) FROM MyTable are optimized to run without a full table scan. However SELECT max(rowid) - min(rowid) FROM MyTable is not, and will perform a full table scan. To achieve the same functionality with the optimizations, try: SELECT (SELECT max(rowid) FROM MyTable) - (SELECT min(rowid) FROM MyTable) SELECT (SELECT max(rowid) FROM MyTable) - 1 - Trey Thank for that link Trey (and Puneet) - it pretty much confirms what I'd arrived at for myself by trial and error. guy - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] performance question: SELECT max(rowid) - 1
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] -
Re: [sqlite] performance question: SELECT max(rowid) - 1
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] -
[sqlite] performance question: SELECT max(rowid) - 1
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] -
