CODEWITHSUNDEEP
phpmysqlperformancepagination
Foreba
Foreba

Reputation: 410

Upgrading a PHP pagination function to run faster

I created a pagination for an website file last year, and I've never really had to use it again.

But now I'll need to use it in a bigger application, and I'm afraid it won't behave really good with a huge amount of mysql rows, I don't know.

I'll post an example bellow and I'll be greatful if someone could tell me if I really need to upgrade the code.

/* Connection file */
include( 'config.php' );

/* Get the amount of all rows */
$mqc = "SELECT COUNT(*) AS total FROM table";
$mqe = mysql_query( $mqc );
$mqn = mysql_fetch_array( $mqe );
$total = $mqn['total'];


/* Rows per page */
$perPage = 10;

/* Range for the pagination */
$range = 5;

/* Actual page */
$page = isset( $_GET['page'] ) &&  
            is_numeric( $_GET['page'] ) && 
            !empty( $_GET['page'] ) ? 
            ( (int)$_GET['page'] * $range ) : 0;

/* Query */
$mqc = "SELECT * FROM table 
            LIMIT " . $page . ", " . $perPage . " ";
$mqe = mysql_query( $mqc );

/* Show results */
while( $l = mysql_fetch_object( $mqe ) )
{
    echo $l->id . ' - ' . $l->title . '<br />';
}

/* Function for pagination */
function pages( $total = false, $perPage = false, $page = false, $range = false )
{
    /* Number of pages */
    $totalPages = ( ceil( $total / $perPage ) ) - 1;
    /* The range * 2 */
    $range = $range;
    /* The real param $_GET['page'] */
    $realGetPage = !empty( $page ) ? ( $page / $range ) : 0;

    /* Show the first page */
    if($realGetPage >= 7)
    {
        echo ' <a href="?page=0"> 1 </a> ... ';
    }

    /* Loop to create the range */
    for($i = ( $realGetPage - $range ); $i < ( $range + $realGetPage ); $i++)
    {
        /* Check out if the value isn't outta range */
        if( $i > 0 && $i < $totalPages )
        {
            /* Highlight the actual page */
            if( ( $i - 1 ) == $realGetPage )
            {
                echo ' [ <a href="?page=' . ( $i - 1 ) . '"> ' . $i . ' </a> ] ';
            }
            else
            {
                echo ' <a href="?page=' . ( $i - 1 ) . '"> ' . $i . ' </a> ';
            }
        }

        /* Store the number of the last page */
        $lastPage = ( $i + 1 );
    }

    /* Hack to show 5678910 */
    if( $lastPage < ( $range * 2 ) + 1 )
    {
        for( $j = $lastPage; $j <= ( $range * 2 ); $j++ )
        {
            if( ($j - 1) == $realGetPage )
            {
                echo ' [ <a href="?page=' . ( $j - 1 ) . '"> ' . $j . ' </a> ] ';
            }
            else
            {
                echo ' <a href="?page=' . ( $j - 1 ) . '"> ' . $j . ' </a> ';
            }
        }

    }

    /* Last page */
    echo ' ...  <a href="?page=' . $totalPages . '"> ' . $totalPages . ' </a> ';
}

/* Show the pagination */
pages( $total, $perPage, $page, $range );

What do you think?

Upvotes: 0

Views: 741

Answers (2)

Tchoupi
Tchoupi

Reputation: 14691

From the information you provided in your comment, the performance issues you might have will not come from your PHP script. If you do multiple INNER JOINS on huge tables, this is exactly what will affect your performance. Some important things to look for:

  • Tables indexes (if you use filters, for your joins if you don't join on primary keys)
  • Optimizing your table / queries to decrease the number of JOINs
  • If you expect a lot of traffic, consider using caching for your queries (memcached for instance)
  • As Phil mentionned, the ORDER BY must be used to render a predictable result and could slow down your queries if the tables are used. You could pre-render ordered table using a cron job
  • Consider using denormalization to reduce the number of JOINS

Upvotes: 2

Phil
Phil

Reputation: 164912

The main thing I see missing is an ORDER BY clause. Without this, your paged results are (potentially) unpredictable.

Add an ORDER BY clause and make sure you have appropriate database indexes in place on the sortable column(s).

I'd also recommend switching to a more up-to-date database connection library with prepared statements and bound parameters. Please see PDO.

Upvotes: 1

Related Questions