Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query:

select qid,ansid,ans from table1 where askerid='something'

This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the next 50 rows should be retrieved. I have thought of a query like this :

select qid,ansid,ans 
from table1 
where askerid='something' 
limit 100
minus 
select qid,ansid,ans 
from table1 
where askerid='something' 
limit 50

I want to know if this is a good query performance-wise and alternate queries that perform better than the above one.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

The proper way to do this is using LIMIT and OFFSET.

Note that for this to work properly, your query will need an ORDER BY clause so that the rows are fetched in the same order each time.

Your first query would be:

SELECT qid,ansid,ans 
FROM table1 
WHERE askerid='something'
LIMIT 50 OFFSET 0

The second query would have an OFFSET of 50, as you want to skip the first 50 rows:

SELECT qid,ansid,ans
FROM table1 
WHERE askerid='something' 
LIMIT 50 OFFSET 50

The ORDER BY clause wants adding between the WHERE and LIMIT clauses.

The Postgres documentation on this is here, and mentions that this may not be performant for large OFFSET values. It will be ok for small-ish datasets if the ORDER BY column is indexed.

share|improve this answer
    
Yeah it says that large offsets can be inefficient. Then suppose I have 10000 rows and I am retrieving 100 rows at a time. Then for the last 100 rows offset - 9900 and limit - 100. So then what is the efficient option for such situations. –  Ashwin Dec 14 '12 at 11:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.