1 Replies - 24319 Views - Last Post: 09 January 2015 - 10:21 PM

#1 SpaceMan   User is offline

  • D.I.C Addict
  • member icon

Reputation: 10
  • View blog
  • Posts: 507
  • Joined: 20-February 03

Speed up MySQL Querys with paging limits.

Posted 23 January 2007 - 06:12 PM

Description: this is a general guide, you will have to modify to use on pages.

working with a data base of 10 million records(ATM) i use this pulling i have seen as many as 400,00 records in one week for display.
i use get's so i can add them to links not to change record display unless i post it.
using ,form with no method defaults to a get no action will default to the page you are on.
although this is writen in php, deals with MySQL query, can de modified to other codes/databases as needed.
Page the results for large amounts of records for displaying.
first thing we want to know, how many records to display by default and calculate the query limit.

<?

if(is_numeric($_GET['MPP']))
     define('MAX_PER_PAGE',$_GET['MPP']);
else {
     define('MAX_PER_PAGE',100);
     $_GET['MPP'] = MAX_PER_PAGE;
}
//add form or links 
echo "<form method='GET' name=mainform  action='{$_SERVER['REQUEST_URI']}'>rn
Max Per Page <input name='MPP' value='".MAX_PER_PAGE."' type='text' size='4'>rn
<input type='Submit'>  </form>rn
";

//if you dont want to specify how many on the form, can just set it.
define('MAX_PER_PAGE',100);
/**
other gets to link so we dont lose where we are, like MPP=max per page
*/
$LINK_THIS = "MPP={$_GET['MPP']}";

/**
ok, now we know how many to display per page.
now we need to know what page we are on. if not on one, display first page.
the page we are on multiplyed by the how many to show per page. 
*/
if(is_numeric($_GET['P']) > 0)
     $Page_sql = "LIMIT " . ($_GET['P'] - 1) * MAX_PER_PAGE . "," . MAX_PER_PAGE;
else     
     $Page_sql = "LIMIT 0," . MAX_PER_PAGE;
/**
To caculate how many pages and display the links etc...., 
we need to know the total record count.
what records do we want to count?
add WHERE to limit to certian records.
*/
if($_GET['TC'] == ''){
        $Result_C = mysql_fetch_array(mysql_query("
        SELECT COUNT(*) FROM `this_table`
        INNER JOIN `this_other_table` USING(ID)
        WHERE Status=1 
        $DATE_RANGE
        "));
     $Counted = $Result_C[0];
}
/**
get total record count and link it so not have to count every page load
*/
if(is_numeric($_GET['TC']) > 0){
     $Counted = $_GET['TC'];
     $LINK_THIS .= "&TC={$_GET['TC']}";
}

/**
float ceil ( float value )
Returns the next highest integer value by rounding up value if necessary. 
The return value of ceil() is still of type float as the value range of float is usually bigger than that of integer. 
*/
$TotalPages = ceil( $Counted / MAX_PER_PAGE);
//what page are we on?
if (is_numeric($_GET['P']) > 0)
     $CurrentPage = $_GET['P'];
else
     $CurrentPage = 1;

unset($Page_Numbers);
/**
now to calculate the pages avalable and display the links, will know link the page you are on.
and to make links for all the pages exept the one we are on.
*/
for($x=1; $x < $TotalPages + 1; $x++) {
     if ($x == $CurrentPage && is_numeric($_GET['P']) > 0) 
          $Page_Numbers .= " <b>[ " . $x . " ]</b> | ";
     else 
          $Page_Numbers .= " <a href="?$LINK_THIS&P={$x}">[{$x}]</a> | ";
}
//remove the last | from the string
$Page_Numbers = rtrim($Page_Numbers," |");
echo "Total:{$Counted} 
PAGE: {$Page_Numbers}";
/**
ok, so now we have the counts, where we are at, how many to display and get the records.
*/
$SQL_D = mysql_query("
SELECT * FROM `this_table`
INNER JOIN `this_other_table` USING(ID)
WHERE Status=1
$DATE_RANGE
$Page_sql
");
WHILE($row = mysql_fetch_object($SQL_D)){
     echo "data to display {$row->ID}";

}
?>


Is This A Good Question/Topic? 0
  • +

Replies To: Speed up MySQL Querys with paging limits.

#2 macosxnerd101   User is offline

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12526
  • View blog
  • Posts: 45,656
  • Joined: 27-December 08

Re: Speed up MySQL Querys with paging limits.

Posted 09 January 2015 - 10:21 PM

Note: The mysql_*() family of functions is deprecated. Please refer to MySQLi or PDO, using prepared statements in particular. I will leave this snippet as the concept is still valid and the mysql_*() functions were valid back in 2008, when this snippet was submitted.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1