loading only few rows from huge table

On Wed, Dec 22, 2004 at 11:31:59 -0800,
Mark <sendmailtomark@yahoo.com> wrote:

Hi,
I have a table with 100K rows. One of columns is a timestamp and
indicates when this row inserted.

What will the the best way of getting 10 latest rows from that table
and introducing partial data retrieval (rows 50-60, 100- 120, etc)

If there is an index on the timestamp column you can efficiently get
the 10 rows with the latest timestamps by using ORDER BY and LIMIT.
Getting rows out of the middle of the table by row number efficiently is
going to be harder. You can use OFFSET to get groups of records starting
from number other than the latest. As the offset gets bigger this will
run slower. If the timestamps are unique and you always continue from
where you left off and you can save the last timestamp you retrieved, then
you can use a WHERE clause to get the offset efficiently.

On Wed, Dec 29, 2004 at 06:54:31 -0800,
Mark <sendmailtomark@yahoo.com> wrote:

In general you should keep replies copied back to the list so that other
people can learn from and contribute to the discussion.

Responding to messages in line makes it easier for people to follow the
discussion. I will mangle your reply to make it easier for other people
to help with your followup question.

--- Bruno Wolff III <bruno@wolff.to> wrote:

On Wed, Dec 22, 2004 at 11:31:59 -0800,
Mark <sendmailtomark@yahoo.com> wrote:

Hi,
I have a table with 100K rows. One of columns is a timestamp and
indicates when this row inserted.

What will the the best way of getting 10 latest rows from that

table

and introducing partial data retrieval (rows 50-60, 100- 120,

etc)

If there is an index on the timestamp column you can efficiently
get
the 10 rows with the latest timestamps by using ORDER BY and LIMIT.
Getting rows out of the middle of the table by row number
efficiently is
going to be harder. You can use OFFSET to get groups of records
starting
from number other than the latest. As the offset gets bigger this
will
run slower. If the timestamps are unique and you always continue
from
where you left off and you can save the last timestamp you
retrieved, then
you can use a WHERE clause to get the offset efficiently.

Thanks this will be fine for C/C++ client side.
Will setMaxRows of java.sql.Statement class do the same job ?

I am not familiar with the java interface and don't know the answer to this.