Pagination for changing data in php and mysql large tables?

Question

I need to handle pagination in PHP and Mysql, Problem statement is as follows:
I have a table with hundreds of thousands records of relationship of 2 foreign keys say "User" and "Item", and this table changes frequently having adding or removing Item for a particular User. Now I would like to list all the Items to the User with pagination and total count in heading. My table is MyISAM and use SQL_CALC_FOUND_ROWS as well. Table structure is as follows with huge cardinality.

CREATE TABLE `USER_ITEMS` (
  `ID` int(11) NOT NULL,
  `ITEMS` int(11) unsigned NOT NULL DEFAULT '0',
  `USER` int(11) unsigned NOT NULL DEFAULT '0',
  `TYPE` char(1) NOT NULL DEFAULT '',
  `TIME` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `SEEN` char(1) NOT NULL,
  `FILTERED` char(1) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `IND1` (`USER`,`ITEMS`),
  KEY `USER` (`USER`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Solution I gave a try:

1. I perform a select on Mysql using limit for all pagination,but we get either duplicate Item if removed on real time or missed Item if any added while pagination.Also many a times it is very slow query due to order by and other conditions leading performance hit. This is simple Select query with where, order by and limit .
2. I used Memcache to store the Items of the pages he viewed and get Items excluding the ones in Memcache during pagination, but this leads to slow query as well.This is Select query with where with ITEMS NOT IN , order by and limit.

Issue are mainly accuracy of data, performance in terms of query execution time and network choking, php memory exhaust.

I would like to know if these are the general practises for the same, or do we have something better either implementation/ engine/ cache for such scenarios.

Answer 1

For pagination without missing/duplicating entries, you need to "remember where you left off" instead of using OFFSET.

That is, for the "next" page, do

WHERE id > $last
ORDER BY id
LIMIT 10

and then keep the last id for the [Next] button. (Since you have not provided your code, I cannot be more specific.)

More details are fund in my pagination blog.

INDEX(user) is redundant since you have UNIQUE(user, items).

Consider getting rid of id since the unique key could be promoted to PK. For iterating though a compound key, see another blog.

Answer 2

I use this pagination for fetching profiles of users, hope this might be helpful for you.

<?php

     $dbhost = 'localhost';
     $dbuser = 'xyz';
     $dbpass = 'vbN';

     $rec_limit = 1;
     $conn = mysql_connect($dbhost, $dbuser, $dbpass);

     if(! $conn )
     {
        die('Could not connect: ' . mysql_error());
     }
     mysql_select_db('xyz');

     /* Get total number of records */
     $sql = "SELECT count(user_id) FROM se_users";
     $retval = mysql_query( $sql, $conn );

     if(! $retval )
     {
        die('Could not get data: ' . mysql_error());
     }
     $row = mysql_fetch_array($retval, MYSQL_NUM );
     $rec_count = $row[0];

     if( isset($_GET{'page'} ) )
     {
        $page = $_GET{'page'} + 1;
        $offset = $rec_limit * $page ;
     }
     else
     {
        $page = 0;
        $offset = 0;
     }
     $left_rec = $rec_count - ($page * $rec_limit);
     $sql = "SELECT company_name, e_mail, international_code, mob_number, nationality, current_loc, pref_location, 
     key_skills, address, user_dob, training, lang1, lang2, lang3, lang4 ".
        "FROM se_job_contracts ".
        "LIMIT $offset, $rec_limit";

     $retval = mysql_query( $sql, $conn );

     if(! $retval )
     {
        die('Could not get data: ' . mysql_error());
     }

     while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
     {
        echo "<b>Company Name</b> :{$row['company_name']}  <br> ".
           "<b>Email-ID</b> : {$row['e_mail']} <br> ".
           "<b>International code </b> : {$row['international_code']}  "."<b>Mobile No </b> : {$row['mob_number']} <br> ".
           "<b>Nationality </b> : {$row['nationality']} <br> ".
           "<b>Current Location </b> : {$row['current_loc']} <br> ".
           "<b>Preferred Location </b> : {$row['pref_location']} <br> ".
           "<b>Key Skills </b> : {$row['key_skills']} <br> ".
           "<b>Address </b> : {$row['address']} <br> ".
           "<b>User DOB </b> : {$row['user_dob']} <br> ".
           "<b>Training  </b> : {$row['training']} <br> ".
           "<b>Language known 1 </b> : {$row['lang1']} <br> ".
           "<b>Language known 2 </b> : {$row['lang2']} <br> ".
           "<b>Language known 3 </b> : {$row['lang3']} <br> ".
           "<b>Language known 4  </b> : {$row['lang4']} <br> ".
           "--------------------------------<br>";
     }

     if( $page > 0 )
     {
        $last = $page - 2;
        echo "<a href=\"$_PHP_SELF?page=$last\">Previous </a> |";
        echo "<a href=\"$_PHP_SELF?page=$page\">Next >></a>";
     }
     else if( $page == 0 )
     {
        echo "<a href=\"$_PHP_SELF?page=$page\">Next >></a>";
        }   
     else if( $left_rec < $rec_limit )
     {
        $last = $page - 2;
        echo "<a href=\"$_PHP_SELF?page=$last\">Previous </a>";
     }
    mysql_close($conn);
  ?>