PHP MySQL pagination is slow

Question

My table

Field   Type    Null    Key Default Extra
id      int(11)     NO  PRI NULL    auto_increment
userid  int(11)     NO  MUL NULL     
title   varchar(50) YES     NULL     
hosting varchar(10) YES     NULL     
zipcode varchar(5)  YES     NULL     
lat     varchar(20) YES     NULL     
long    varchar(20) YES     NULL     
msg    varchar(1000)YES MUL NULL     
time    datetime    NO      NULL     

That is the table. I have simulated 500k rows of data and deleted randomly 270k rows to leave only 230k with an auto increment of 500k.

Here are my indexs

Keyname Type    Unique  Packed  Field   Cardinality Collation   Null
PRIMARY BTREE   Yes No  id            232377    A       
info    BTREE   No  No  userid          2003    A       
lat                                    25819    A   YES
long                                   25819    A   YES
title                                  25819    A   YES
time                                   25819    A   

With that in mind , here is my query:

SELECT * FROM posts WHERE long>-118.13902802886 AND long<-118.08130797114 AND lat>33.79987197114 AND lat<33.85759202886 ORDER BY id ASC LIMIT 0, 25

Showing rows 0 - 15 (16 total, Query took 1.5655 sec) [id: 32846 - 540342]

The query only brought me 1 page, but because it had to search all 230k records it still took 1.5 seconds.

Here is the query explained:

id  select_type table   type    possible_keys   key     key_len ref rows    Extra
1   SIMPLE      posts   index   NULL            PRIMARY 4       NULL 25     Using where

So even if i use where clauses to only get back 16 results I still get a slow query.

Now for example if i do a broader search :

SELECT * FROM `posts` WHERE `long`>-118.2544681443 AND `long`<-117.9658678557 AND `lat`>33.6844318557 AND `lat`<33.9730321443 ORDER BY id ASC LIMIT 0, 25

Showing rows 0 - 24 (25 total, Query took 0.0849 sec) [id: 691 - 29818]

It is much faster when retrieving the first page out of 20 pages and 483 found total but i limit to 25.

but if i ask for the last page

SELECT * FROM `posts` WHERE `long`>-118.2544681443 AND `long`<-117.9658678557 AND `lat`>33.6844318557 AND `lat`<33.9730321443 ORDER BY id ASC LIMIT 475, 25

Showing rows 0 - 7 (8 total, Query took 1.5874 sec) [id: 553198 - 559593]

I get a slow query.

My question is how do I achieve good pagination? When the website goes live I expect when it takes off that posts will be deleted and made daily by the hundreds. Posts should be ordered by id or timestamp and Id is not sequential because some records will be deleted. I want to have a standard pagination

1 2 3 4 5 6 7 8 ... [Last Page]

Answer 1

Filter from your results records which appeared on earlier pages by using a WHERE clause: then you do not need to specify an offset, only a row count. For example, keep track of the last id or timestamp seen and filter for only those records with id or timestamp greater than that.