dimgray

Efficient SQLite Pagination

LIMIT and OFFSET come first to mind when doing pagination, but these will incur performance regression. This is how I paginate efficiently in PHP:

<?php
# Since the number of posts per page is fixed, we define a constant.
define('POSTS_PER_PAGE', 10);

# Instantiate a database connection. Here I'm using SQLite.
$db = new SQLite3('/PATH/TO/my_database.sqlite');

# We usually GET page number via the query string.
# https://example.com/?page_number=123
$page_number = filter_input(INPUT_GET, 'page_number', FILTER_VALIDATE_INT) ;

# Blogs are usually sorted with the newest post coming first.
# So we grab the row id of latest post.
# In my blog, I have no wasted row, so I simply sort by row id. 😁
# *If you have complicated filtering, use ROW_NUMBER() to re-order your row ids.
$last_row_id_sql = 'SELECT id FROM my_posts_table ORDER BY id DESC LIMIT 1';
$last_row_id = $db->querySingle($last_row_id_sql);

# Advancing to next pages would alter the row id of last-est post of the page.
$last_row_id = $last_row_id - (POSTS_PER_PAGE * ($page_number - 1));

# Finally, we can fetch the 10 posts per paginated page.
$posts_sql = 'SELECT * FROM my_posts_table WHERE id <= :id ORDER BY id DESC LIMIT ' . POSTS_PER_PAGE;        
$statement = $db->prepare($posts_sql);
$statement->bindValue(':id', $last_row_id, SQLITE3_INTEGER);
$posts = $statement->execute();

# Use the result in your output template, like so. ?>
<?php while ($post = $posts->fetchArray(SQLITE3_ASSOC)) : ?>
    <article><?= $post['content']; ?></article>
<?php endwhile; ?>
<?php
# BONUS: This will check if the current page has no post.
if (false != $posts->fetchArray()) {
    $posts->reset();
    // This page has post(s). Do something ...
} else {
    // This page has NO post. Do something else ...
}

# Close the database connection.
$db->close();

This has been a simplified tutorial. I hope you benefited from it. Mucho gracias!

*SQLite Window Function: ROW_NUMBER()