Discussion on paging processing of mysql| paging Php+mysql
A common paging process is:
1. Get the total number of records to be paged with select COUNT (*) from Tbl_name
2, according to the number of records per page calculated total Page: Total number of pages = Ceil (Total records/per page record number)
3, according to the current page number to calculate the starting position: Starting position = (Current page number-1) * Record number per page
4, with SELECT * from tbl_name limit starting position, each page record number to obtain the record to be displayed
5, List output related information
In this process, the database needs to traverse the table two times to get the required data. Although limit will terminate the traversal after getting the specified number of records, the previous search until the "starting position" is wasted.
Here we propose a new algorithm to discuss with you:
1, the use of MySQL user variables, split and extract the start of each page ID number.
2, the number of records of the results of the query is total pages
3, according to the current page number to get the current page start ID
4, with SELECT * from Tbl_name where id>= start ID limit the number of records per page to get the record to be displayed
5, List output related information
You can see that in the last query. Because the ID is used as the primary key feature, the database can be positioned directly to the desired record. Thus reducing the query time.
This query algorithm has a by-product: you can produce a duplicate record that joins the next page, which is an overlapping record between pages. Of course, it's easy to get rid of him.
Here is the test code:
<?php
Include "Mysql_result_all.inc"; A work function to display query results
$pagesize = 9; Number of rows per page
$type = 1; =1 descending order
$mode = 0; =1 does not repeat the last record on the previous page
$conn = mysql_connect (); Connect MySQL
mysql_select_db ("site"); Select Database
Preparing to dynamically modify query strings
if ($type) {
$order = "desc";
$expr = "<=";
}else {
$order = "";
$expr = ">=";
}
if ($mode) $pagesize + +;
mysql_query ("Set @v:=-1"); To define a MySQL user variable
$rs = mysql_query ("Select @v:= (@v+1) as XH, ID from data has mod (XH, $pagesize) =0 ORDER by ID $order");
Mysql_result_all ($RS); Check each page distribution
echo $pages = mysql_num_rows ($RS); Get Total Pages
if ($mode) $pagesize--;
To test the paging results, $i represent the display page
for ($i =0; $i < $pages; $i + +) {
Mysql_data_seek ($rs, $i); Move result set pointer
List ($xh, $id) = Mysql_fetch_row ($rs); Get Start ID
echo "<br>[$i] $xh--$id";
$rs 1 = mysql_query ("SELECT * from Data where id$expr$id order by ID $order limit $pagesize");
Mysql_result_all ($rs 1); Show related results
}
?>
Mysql_result_all.inc
I've posted this function many times and it's very useful for debugging programs.
?
function Mysql_result_all ($result, $format = "") {
echo "<table $format ><tr>";
for ($i =0; $i <mysql_num_fields ($result); $i + +) {
echo "<th>". Mysql_field_name ($result, $i). " </th> ";
}
echo "</tr>";
while ($row = Mysql_fetch_row ($result)) {
echo "</tr>";
for ($i =0; $i <mysql_num_fields ($result); $i + +) {
echo "<td>". $row [$i]. " </td> ";
}
echo "</tr>";
}
echo "</table>";
}
?>
