MySQL Millions data paging optimization

PHP Write paging function, as long as the use of MySQL, is basically two steps away

1. Total number of pages. SQL statements are naturally

The code is as follows	Copy Code
SELECT Count (*) from TableName;

2, according to the specified number of page number, to obtain the appropriate data. The corresponding SQL statements, the online search, are the same:

SELECT f1,f2 from table LIMIT offset,length

Instance Paging class

The code is as follows

Copy Code

<?php /********************************************* Class Name: Pagesupport Features: Paging display of data in MySQL database ***********************************************/ Class pagesupport{ Property var $sql; The SQL query statement to display the data var $page _size; Show maximum number of rows per page var $start _index; The first line ordinal of the record you want to display var $total _records; Total Records var $current _records; Number of records read on this page var $result; read out the results var $total _pages; Total pages var $current _page; Current page var $display _count = 30; The first few pages displayed and the last few pages var $arr _page_query; An array that contains pagination to display the parameters that need to be passed var $first; var $prev; var $next; var $last; Method /********************************************* Constructors: __construct () Input parameters: $ppage _size: Show the maximum number of rows per page ***********************************************/ function Pagesupport ($ppage _size) { $this->page_size= $ppage _size; $this->start_index=0; } /********************************************* Constructors: __destruct () Input parameters: ***********************************************/ function __destruct () { } /********************************************* Get function: __get () ***********************************************/ function __get ($property _name) { if (Isset ($this-> $property _name)) { Return ($this-> $property _name); } Else { return (NULL); } } /********************************************* Set function: __set () ***********************************************/ function __set ($property _name, $value) { $this-> $property _name = $value; } /********************************************* Function Name: Read_data Function: Read the corresponding record from the table according to the SQL query statement Return value: Property two-dimensional array result[record number [field name] ***********************************************/ function Read_data () { $psql = $this->sql; Query data, database links and other information should be implemented outside the class call $result =mysql_query ($psql) or Die (Mysql_error ()); $this->total_records=mysql_num_rows ($result); Use the Limit keyword to get the records that are displayed on this page if ($this->total_records>0) { $this->start_index = ($this->current_page-1) * $this->page_size; $psql = $psql. "LIMIT". $this->start_index. "," $this->page_size; $result =mysql_query ($psql) or Die (Mysql_error ()); $this->current_records=mysql_num_rows ($result); Place query results in a result array $i = 0; while ($row =mysql_fetch_array ($result)) { $this->result[$i]= $row; $i + +; } } Get total pages, current page information $this->total_pages=ceil ($this->total_records/$this->page_size); $this->first=1; $this->prev= $this->current_page-1; $this->next= $this->current_page+1; $this->last= $this->total_pages; } /********************************************* Function name: Standard_navigate () Function: Display the first page, the next page, the top, the not page ***********************************************/ function Standard_navigate () { echo "<div align=center>"; echo "<form action=". $_server[' php_self '. "method=" get ">"; echo "<font color = red size = ' 4 ' >". $this->current_page. " Page/Total ". $this->total_pages." Page </font> "; echo ""; echo jumps to <input type= "text" Size=ř "name=" Current_page "value=". $this->current_page. "' /> page "; echo "<input type=" Submit "value=" submitted "/>"; Generate navigation links if ($this->current_page > 1) { echo "<a href=". $_server[' php_self '. Current_page= ". $this->first." > Home </a>| "; echo "<a href=". $_server[' php_self '. Current_page= ". $this->prev." > On a page </a>| "; } if ($this->current_page < $this->total_pages) { echo "<a href=". $_server[' php_self '. Current_page= ". $this->next." > next page </a>| "; echo "<a href=". $_server[' php_self '. Current_page= ". $this->last." > Last </A> "; } echo "</form>"; echo "</div>"; } /********************************************* Function name: Full_navigate () Function: Display the first page, the next page, the top, the not page Generate navigation links such as 1 2 3 ... 10 11 ***********************************************/ function Full_navigate () { echo "<div align=center>"; echo "<form action=". $_server[' php_self '. "method=" get ">"; echo "<font color = red size = ' 4 ' >". $this->current_page. " Page/Total ". $this->total_pages." Page </font> "; echo ""; echo jumps to <input type= "text" Size=ř "name=" Current_page "value=". $this->current_page. "' /> page "; echo "<input type=" Submit "value=" submitted "/>"; Generate navigation links such as 1 2 3 ... 10 11 $front _start = 1; if ($this->current_page > $this->display_count) { $front _start = $this->current_page-$this->display_count; } for ($i = $front _start; $i < $this->current_page; $i + +) { echo "<a href=". $_server[' php_self '. Page= ". $i." >[". $i."] </a> "; } echo "[". $this->current_page. "]"; $displayCount = $this->display_count; if ($this->total_pages > $displayCount && ($this->current_page+ $displayCount) < $this->total_ Pages) { $displayCount = $this->current_page+ $displayCount; }else{ $displayCount = $this->total_pages; } for ($i = $this->current_page+1; $i <= $displayCount; $i + +) { echo "<a href=". $_server[' php_self '. Current_page= ". $i." >[". $i."] </a> "; } Generate navigation links if ($this->current_page > 1) { echo "<a href=". $_server[' php_self '. Current_page= ". $this->first." > Home </a>| "; echo "<a href=". $_server[' php_self '. Current_page= ". $this->prev." > On a page </a>| "; } if ($this->current_page < $this->total_pages) { echo "<a href=". $_server[' php_self '. Current_page= ". $this->next." > next page </a>| "; echo "<a href=". $_server[' php_self '. Current_page= ". $this->last." > Last </A> "; } echo "</form>"; echo "</div>"; } } ?> Call: <?php Include_once (".. /config_jj/sys_conf.inc "); Include_once (".. /pagesupportclass.php ");//Paging class Include_once ('.. /smarty_jsnhclass.php '); $smarty = new SMARTY_JSNH (); Include_once (".. /include/smarty_changed_dir.php "); $smarty->assign (' title ', "Smarty News pagination Test"); <?php $pageSupport = new Pagesupport ($PAGE _size); Instantiating a Pagesupport object $current _page=$_get["Current_page"];//Paging current page if (Isset ($current _page)) { $pageSupport->__set ("Current_page", $current _page); } else { $pageSupport->__set ("Current_page", 1); } ?> $pageSupport->__set ("SQL", "SELECT * from News"); $pageSupport->read_data ()//Read data if ($pageSupport->current_records > 0)///If the data is not empty, assemble the data { For ($i =0 $i < $pageSupport->current_records; $i + +) { $title = $pageSupport->result[$i] ["title"]; $id = $pageSupport->result[$i] ["id"]; $news _arr[$i] = Array (' News ' => Array (' ID ' => $id, ' title ' => $title)); } } Close Database Mysql_close ($DB); $pageinfo _arr = Array ( ' Total_records ' => $pageSupport->total_records, ' Current_page ' => $pageSupport->current_page, ' Total_pages ' => $pageSupport->total_pages, ' => $pageSupport->first, ' prev ' => $pageSupport->prev, ' Next ' => $pageSupport->next, ' Last ' => $pageSupport->last ); $smarty->assign (' Results ', $news _arr); $smarty->assign (' Pagesupport ', $pageinfo _arr); $smarty->display (' News/list.tpl '); ?> Template List.tpl {* I am a Smarty comment, I don ' t exist in the compiled output *} {* {$pageSupport .total_records}<br/> {$pageSupport .current_page}<br/> {$pageSupport .total_pages}<br/> {$pageSupport .first}<br/> {$pageSupport .prev}<br/> {$pageSupport .next}<br/> {$pageSupport .last}<br/> *} <meta http-equiv= "Content-type" content= "text/html; CHARSET=GBK "/> <title>{$title}</title> <body> {foreach item=o from= $results} {$o. News.id} {$o. News.title} <br> {Foreachelse} There is no data you want to view! {/foreach} <br/> {if ($pageSupport. total_records > 0)} <form action= "" method= "get" > Total {$pageSupport. Total_records} Records $pageSupport. Current_page} page/Total {$pageSupport. total_pages} page {if ($pageSupport. current_page > 1)} <a href=?current_page={$pageSupport .first}> Home </A> <a href=?current_page={$pageSupport .prev}> prev </A> {/if} {if ($pageSupport. current_page < $pageSupport. Total_pages)} <a href=?current_page={$pageSupport .next}> next page </A> <a href=?current_page={$pageSupport .last}> last page </A> {/if} Skip to <input type= "text" size= "4" name= "Current_page" value= "{$pageSupport. Current_page}"/> Page <input type= "Submit" value= "Go"/> </form> {/if} </body>

Grammar, do not explain, when the amount of data is small, so write, nothing.

What if the amount of data is large? Not generally big, millions.

Try to run it:

The code is as follows	Copy Code
SELECT ID from users LIMIT 1000000,10

On my computer, run for the first time and display as follows:

Rows in Set (9.38 sec)

And then run it again, as shown below:

Rows in Set (0.38 sec)

It's not surprising. MySQL buffers The SQL statements that are already running and can quickly get the data out.

In any case, the first 9 seconds are more than I can accept.

Change the wording:

The code is as follows	Copy Code
SELECT ID from users WHERE id>1000000 LIMIT 10;

Showing: Rows in Set (0.00 sec)

In fact, phpMyAdmin to see, "Show row 0-9 (10 total, query cost 0.0011 seconds)", and then run, basically in 0.0003 seconds or so.

Millions optimization

To optimize the query, avoid full table scans as much as possible, and first consider establishing an index on the where and the columns involved.

2. The null value of the field in the WHERE clause should be avoided as far as possible, or it will cause the engine to discard the use of the index for a full table scan, such as:

The code is as follows	Copy Code
Select ID from t where num is null

You can set the default value of 0 on NUM to ensure that the NUM column in the table does not have a null value and then query this way:

The code is as follows	Copy Code
Select ID from t where num=0

3. The use of!= or <> operators in the WHERE clause should be avoided as far as possible, otherwise the engine discards the use of the index for a full table scan.

4. You should try to avoid using or to join conditions in the WHERE clause, or it will cause the engine to discard the use of the index for a full table scan, such as:

The code is as follows	Copy Code
Select ID from t where num=10 or num=20

You can query this way:

The code is as follows	Copy Code
Select ID from t where num=10 UNION ALL Select ID from t where num=20

5.in and not in should also be used with caution, otherwise it will result in a full table scan, such as:

　

The code is as follows	Copy Code
Select ID from t where num in (1,2,3)

For consecutive values, you can use between instead of in:

　　

The code is as follows	Copy Code
Select ID from t where num between 1 and 3

6. The following query will also result in a full table scan:

　

The code is as follows	Copy Code
Select ID from t where name like '%abc% '

Classification function

The code is as follows

Copy Code

$db =dblink (); $db->pagesize=20; $sql = "SELECT ID from collect where vtype= $vtype"; $db->execute ($sql); $strpage = $db->strpage (); Save the paging string in a temporary variable for easy output while ($rs = $db->fetch_array ()) { $strid. = $rs [' id ']. ', '; } $strid =substr ($strid, 0,strlen ($strid)-1); Construct an ID string $db->pagesize=0; It is critical that, without logging off the class, the paging will be emptied, so that only one database connection is required, and no further opening is required; $db->execute ("Select Id,title,url,stime,gtime,vtype,tag from collect where ID in ($strid)"); <?php while ($rs = $db->fetch_array ()):?> <tr> <td>&nbsp;<?php echo $rs [' id '];? ></td> <td>&nbsp;<?php echo $rs [' url '];? ></td> <td>&nbsp;<?php echo $rs [' stime '];? ></td> <td>&nbsp;<?php echo $rs [' gtime '];? ></td> <td>&nbsp;<?php echo $rs [' vtype '];? ></td> <td>&nbsp;<a href= "? act=show&id=<?php echo $rs [' id '];? > "target=" _blank "><?php echo $rs [' title '];? ></a></td> <td>&nbsp;<?php echo $rs [' tag '];? ></td> </tr> <?php Endwhile;?> </table> <?php Echo $strpage;