Select from a SQL table starting with a certain index?

Question

I'm new to SQL (using postgreSQL) and I've written a java program that selects from a large table and performs a few functions. The problem is that when I run the program I get a java OutOfMemoryError because the table is simply too big. I know that I can select from the beginning of the table using the LIMIT operator, but is there a way I can start the selection from a certain index where I left off with the LIMIT command? Thanks!

Try one of these: https://www.google.com/search?q=mysql+paging — Yuriy Galanter, Jul 22 '13 at 18:36
@LuisLL guessing MySQL based on *left off with the LIMIT command* — Conrad Frix, Jul 22 '13 at 18:37

score 2 · Accepted Answer · answered Jul 22 '13 at 18:55

2

There is offset option in Postgres as in:

select from table
offset 50
limit 50

answered Jul 22 '13 at 18:55

jny

8,007
3
37
56

score 1 · Answer 2 · answered Jul 22 '13 at 18:39

1

For mysql you can use the follwoing approaches:

SELECT * FROM table LIMIT {offset}, row_count
SELECT * FROM table WHERE id > {max_id_from_the previous_selection} LIMIT row_count. First max_id_from_the previous_selection = 0.

answered Jul 22 '13 at 18:39

Andrej

7,474
1
19
21

score 0 · Answer 3 · edited May 23 '17 at 12:03

This is actually something that the jdbc driver will handle for you transparently. You can actually stream the result set instead of loading it all into memory at once. To do this in MySQL, you need to follow the instructions here: http://javaquirks.blogspot.com/2007/12/mysql-streaming-result-set.html

Basically when you create you call connection.prepareStatement you need to pass ResultSet.TYPE_FORWARD_ONLY and ResultSet.CONCUR_READ_ONLY as the second and third parameters, then call setFetchSize(Integer.MIN_VALUE) on your PreparedStatement object.

There are similar instructions for doing this with other databases which I could iterate if needed.

EDIT: now we know you need instructions for PostgreSQL. Follow the instructions here: How to read all rows from huge table?

Select from a SQL table starting with a certain index?

3 Answers3