5

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.

Yves M.
  • 29,855
  • 23
  • 108
  • 144
a Learner
  • 4,944
  • 10
  • 53
  • 89
  • looks similar to this question: http://stackoverflow.com/questions/1243952/how-can-i-speed-up-a-mysql-query-with-a-large-offset-in-the-limit-clause – Kevin Burton Sep 29 '11 at 10:07

2 Answers2

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

Tom H
  • 46,766
  • 14
  • 87
  • 128
  • when we run "select * from table_name where 'some condition'" then which of the following statement right? ...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 – a Learner Sep 29 '11 at 08:50
  • The server will only return the rows that are output from the query. The client shouldn't be doing any filtering itself. – Tom H Sep 29 '11 at 11:47
1

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

Robert Martin
  • 16,759
  • 15
  • 61
  • 87
  • i have several thousands of records and i have read somewhere that when we run a query like "select * from table_name limit 1000000, 100", in which 'offset' is very large, is inefficient to use since this will still traverse through first 1000000 rows to return the next 100 ones.so i want to avoid the offset while using 'limit' clause. – a Learner Sep 29 '11 at 08:59
  • @brightness This is not a problem as long as you have an index on some_col. With an index, the columns are "sorted" in SQL's mind. Thus, returning rows 1-100 or 1000001-1000100 is the same difficulty. It will simply invoke a binary search through the index, which will take log(N) time. – Robert Martin Sep 29 '11 at 19:54