0

I have an PHP (5.5) webapp with sqlite 3. It has an logs page (possibly 10k to 100k logs in the future). I am using this way of paging for those logs. The query for sqlite I am using:

SELECT *
FROM Logs
WHERE date > [input epoch long]
ORDER BY date DESC
LIMIT 100;

The logs model is like: 

"id": [int increased by sqlite]
"date":[entry date, epoch long like: "1533595828"]
+ other not relevant data for this issue

I ideally want to have an page Like: Previous | 1 | 2 | 3 | 4 | Next, this seams pretty hard. So an Previous and Next page handling is acceptable.

For the Next page handling, I am using: date > [the last record it returned at init]. This works. Bud to go back I need to keep track of the first date. After I go back multiple times, this gets messy.

What would be the best way to solve this problem?

BramscoChill
  • 383
  • 4
  • 17
  • You treat going back just like going forward, except instead of the first 100 rows after the last entry on the current display, you use the 100 entries just before the first entry. (And if you can have multiple rows with the same timestamp, use the two column comparison also mentioned in that link) – Shawn Sep 01 '18 at 09:03
  • Another option is to have a table that holds the starting row for each page, but this works best on static or mostly static data - every time you add an entry you have to check to see if a new page is started and add to the index if so. It does let you jump efficiently to an arbitrary page, though. – Shawn Sep 01 '18 at 09:16

1 Answers1

1

Nobody will want to page through thousands of pages; your webapp could be improved with better searching and filtering.

Anyway, if you want to know how many pages there are, you have to count all rows:

SELECT count(*) FROM Logs;

And if you want to know on which page you are, you have to count how many rows there are before the current one:

SELECT count(*) FROM Logs WHERE date < [first on current page];

The "next" and "previous" functions work in exactly the opposite way of each other: you have to keep track of the last/first row of the current page, and you have to fetch the next 100 rows with a larger/smaller value:

SELECT *                              SELECT *
FROM Logs                             FROM Logs
WHERE date > [last on current page]   WHERE date < [first on current page]
ORDER BY date DESC                    ORDER BY date ASC
LIMIT 100;                            LIMIT 100;

(The second query has the interesting wrinkle of returning the rows in backward order, but that should not be a problem.)

And if you want to jump to a specific page, the easiest way to find out the first date value on that page is with OFFSET:

SELECT date
FROM Logs
ORDER BY date ASC
OFFSET [page*100]
LIMIT 1;

(This query will need to go through these rows, but if the column is indexed, at least it does not have to read any table data.)

CL.
  • 173,858
  • 17
  • 217
  • 259