retrieving rows in reverse direction from table using sql limit

I am implementing pagination using SQL limit in MySQL db.Records are needed to get

retrieved 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.

SELECT * FROM tbl_name ORDER BY some_col DESC

will retrieve all records.

But I do not want all records, as they are thousands in number.

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.

SELECT
    <column list since I never use *>
FROM
    My_Table
ORDER BY
    some_column DESC
LIMIT 500, 500

EDIT: Regarding your comment to Robert's answer... Performance will degrade as the offset gets larger, but the point where the degradation is noticeable is usually pretty large. From an answer that I gave to a similar question on paging a day or two ago:

I am of the strong opinion that a UI should NEVER allow a user to retrieve a set of records that let them go to (for example) page 5000. That's simply too much data for a human mind to find useful all at once and should require further filtering. Maybe let them see the first 100 pages (or some other number), but otherwise they have to constrain the results better. Just my opinion though.

The answer is confusing with your hypothetical numbers, so let me make my own:

Let's say we want to display page 3 with 10 records per page. We want to offset by 20 records (the ones on pages 1 & 2), then find 10 records. The format is:

> SELECT * FROM tbl_name ORDER BY some_col DESC LIMIT 20, 10;

In other words, it's

LIMIT <offset>, <max results>

read more: http://php.about.com/od/mysqlcommands/g/Limit_sql.htm