English
English
When developing web applications, pagination query is one of the very common needs, especially in modules such as backend management systems, comment sections or product lists that need to display a large amount of data. MySQL provides the LIMIT clause for paging, while the PDOStatement::rowCount() method in PDO of PHP can be used to obtain the number of result sets. If the two are combined properly, it can not only improve the readability of the code, but also significantly optimize query efficiency.
Traditional pagination query usually has two steps:
Get the data of the current page: Use LIMIT offset, count .
Get the total number of records: Use SELECT COUNT(*) FROM table .
Although this method is very common, when the data table is large, the overhead of COUNT(*) can be very high, especially in high concurrency environments, which can put a lot of pressure on the database.
PDOStatement::rowCount() is inconsistent in SELECT query. It is not used to count the total number of records, but to return the number of rows affected by the statement executed this time. When using SELECT , some databases (such as PostgreSQL) support it to return the number of rows for the query result, but in MySQL, it is possible to return an accurate number of results only if the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY option is set.
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass', [
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
]);
Although rowCount() cannot be fully relied on to get the total, we can combine LIMIT and intelligent logic to optimize the pagination experience in some scenarios. For example, we can query one more data per page to determine whether there is a next page, thereby avoiding the COUNT(*) operation.
<?php
$pdo = new PDO('mysql:host=localhost;dbname=exampledb', 'user', 'password', [
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$pageSize = 10;
$offset = ($page - 1) * $pageSize;
$limitPlusOne = $pageSize + 1;
$sql = "SELECT * FROM articles ORDER BY created_at DESC LIMIT :offset, :limit";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $limitPlusOne, PDO::PARAM_INT);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
$hasNextPage = false;
if (count($results) > $pageSize) {
$hasNextPage = true;
array_pop($results); // Remove the excess
}
// Rendering data
foreach ($results as $article) {
echo "<h2>{$article['title']}</h2>";
echo "<p>{$article['summary']}</p>";
echo "<a href=\"https://gitbox.net/articles/{$article['id']}\">Read the full text</a><hr>";
}
if ($hasNextPage) {
echo "<a href=\"https://gitbox.net/list?page=" . ($page + 1) . "\">Next page</a>";
}
?>
Avoid COUNT(*) queries and reduce database pressure;
Use LIMIT N+1 to determine whether there is a next page;
The data processing logic is concise and the performance is good;
It is especially suitable for scenarios with large data volume and low paging depth.
Although the above method is efficient, it is not applicable to all situations. If your page needs to display information such as total page count, total record count, etc., then it is inevitable to use COUNT(*) :
$totalCount = $pdo->query("SELECT COUNT(*) FROM articles")->fetchColumn();
$totalPages = ceil($totalCount / $pageSize);
One compromise is to cache the total number of information and refresh it regularly to avoid real-time calculations every time.
There is no "one-size-fits-all" best practice when doing paging optimization. Reasonably combining LIMIT , rowCount() and business needs is the key to high-performance paging query. If you are pursuing extreme performance, you can consider "cursor-based pagination" based on primary keys or timestamps, which performs better under large data sets.
With the tips introduced in this article, you can avoid expensive COUNT(*) queries in most scenarios while maintaining good user experience and paging accuracy.
