all,
thanks to all for the feedback, it was certainly useful. it's not as top prioriy, but i think i will want to work on this if i have time. fyi, for this case i'm using postgresql.
Gene Amtower wrote:
Burak,I brushed over your original post without responding, but Michael's response got me to thinking about it again. I believe the LIMIT and OFFSET clauses are the correct way to approach this problem - any performance issues with this query statement format reside appropriately with the database engine and table objects, not in your use of the LIMIT and OFFSET format.
offset has o(n) worst-case complexity, whatever you do. where clause will use indexes where feasible. offset will never use indexes. so, no it's not a db issue, the database is doing what's as expected. offset clauses should be avoided like plague. (unless you're using oracle, it seems. i'll dig more into this)
Michael correctly identifies a number of issues with using a "WHERE x >" clause on a non-unique key column. If you use LIMIT and OFFSET with an appropriate ORDER BY clause (including a unique key column as the last ORDER BY parameter), you can be sure that LIMIT and OFFSET will always return a consistently-ordered set of rows where the OFFSET amount is consistent and predictable.
i would not want to give sorting ability on non-indexed columns to clients. i agree with you and michael here.
I'm surprised that there's a performance penalty from using the LIMIT and OFFSET format over the "greater than" form, but I'm not sure which database you're using on the back end.
again, where clauses use indexes where feasible. offsets don't.
If this is MySQL, then it doesn't actually have separate LIMIT and OFFSET keywords, instead using two parameters to the LIMIT clause, as in "LIMIT 10,5".
same story, different syntax -- it seems. best regards, burak
------------------------------------------------------------------------------ OpenSolaris 2009.06 is a cutting edge operating system for enterprises looking to deploy the next generation of Solaris that includes the latest innovations from Sun and the OpenSource community. Download a copy and enjoy capabilities such as Networking, Storage and Virtualization. Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________ qooxdoo-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/qooxdoo-devel
