Thread: retrieving rows in reverse direction from db

Hi!

I am implementing pagination using Sql limit in mysql and i want to retrieve records from the last row backwards. There are several thousands records in table

and i want to retrieve say 500 records at a time to show up in a page.


If i use "select * from tbl_name order by some_col desc limit 500" it will return

last 500 records in descending order.

but i could not find a way to retrieve next block of 500 in reverse direction,

starting from a point where first block have left up.

Hi Copeg!

I am using MySql for database and JDBC for programming purpose

MySQL LIMIT / OFFSET:
MySQL :: MySQL 5.5 Reference Manual :: 12.2.9 SELECT Syntax

when we run "select * from table_name where 'some condition'" then all records from 'select' first goes into main memory of our/client computer then 'where' clause is applied there to return selected rows OR 'where' is directly applied at database server and only rows that satisfy the condition are returned to client.

all records from 'select' first goes into main memory

It's not quite that simple, nor that brutal. If you're using MySQL and you want to know in detail how a query works, use a MySQL client that allows you to enter SQL statements directly and add "EXPLAIN " to the front of your SQL. MySQL will tell you in detail what it does to produce your results. It's a great first stop for optimising your queries.

MySQL :: MySQL 5.5 Reference Manual :: 7.8 Understanding the Query Execution Plan