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.
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'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. 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". So, I'm
wondering if you're using something else as your database. Any modern
database program should optimize the LIMIT and OFFSET query structure to
reduce any performance degradation (after all, that's why they provide
that format), but maybe you're using an older version of your database
product. If so, consider upgrading to a newer version that has improved
query optimization. I know that MySQL performs extremely well in SELECT
queries, so the latest MySQL version should give you reasonable
performance, if everything else is done correctly.
Another possible problem is that you might need an index defined within
the database on the columns you're going to use for your ORDER BY
clause. Not having this crucial index on a large database table may be
responsible for the performance problem with the LIMIT and OFFSET usage.
Also, be sure you understand how your database program uses indexes
against any given SELECT query, as some queries may ignore the index
altogether based on the ORDER BY parameter sequence, WHERE parameter
sequence, joins, "like" clauses, or other seemingly minor differences in
your query. If you have an index on your table, you need to be sure
it's actually being used in the database query - don't assume it's being
If you routinely SELECT from a table using various ORDER BY clauses
(maybe because you need to be able to sort the data on several
columns?), you will probably need to define a separate index on each set
of ORDER BY clauses that you use. If you filter on a table using a
WHERE clause, the order of your filter parameters will impact the use of
indexes. If you're performing joins in your query, try to understand if
the join order is interfering with the use of the index. Watch out when
including any SQL functions that modify a column value within the query,
like applying an uppercase function on a column. If these are used in
your where clause, then can prevent the use of a table index because the
index uses only raw column values.
Make sure you're properly addressing the need for appropriate column
indexes in any database implementation, and you will minimize any
performance issues you might experience in your queries.
If I understand correctly,
On Thu, 2009-06-04 at 18:48 +0200, Michael Helwig wrote:
> Hi Burak,
> I tried to do that initially. However, it seemed to me that this is only
> a viable solution if data gets sorted by id. Everything gets quite
> complicated if you try to sort a table by other entries, e.g.
> alphabetically, let's say by a column named "title". Then you would have
> to save the last title value of the last row returned by your last query
> and find all entries following alphabetically - which only works if
> there are no duplicate entries, otherwise you would have to save the
> ids, too. Furthermore, you would have to deal with the possibility of
> null entries in title values, so it is not that easy to make the right
> kind of database queries.
> Maybe these are only minor problems but there seem to be some of these.
> I stopped at some point when trying to implement your suggestion. It may
> be worth the effort with very large databases, but I'm not sure about
> that. However, I would be interested to know if someone made a succesful
> approach on this.
> Burak Arslan schrieb:
> > all,
> > the current method to produce responses to remote table models is to use
> > the underlying database's limit & offset method. while being the
> > simplest solution, offsetting is either
> > 1) slow
> > 2) fast when the query is cached on the server side, but then it's
> > costly in memory.
> > i don't think this problem has a %100 solution.
> > but i see an optimization opportunity: the table could make the job of
> > the database management system much easier by sending the sorting key
> > from the last row of the previous block, if it's already fetched. (or
> > analogously, the sorting key from the first row of the next block, if
> > it's already fetched.
> > this way, the sql query can be:
> > (...) where (...) and id > 200000 limit 50
> > instead of
> > (...) limit 50 offset 128475
> > the former query will instantly sweep off the initial rows, while the
> > latter will iterate over the initial rows only to ignore them.
> > do you think implementing this would be worth the effort ?
> > awaiting your feedback,
> > 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
> > qooxdoo-devel@...
> > https://lists.sourceforge.net/lists/listinfo/qooxdoo-devel
> 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