Php & mysql pagination script for a huge website

advertisements

I have a big database with over 3 million rows and i need a php&mysql pagination script. I already have one but after i pass the level of 1000 pages it's working very slow like minutes to load. Any script/advice that can help me is welcomed.

Edit, im using this script

<?php
// Script de paginare, de la http://www.marplo.net

// Datele pt. conectare la baza de date
// MODIFICATI
$host = "localhost";    // server MySQL
$utilizator = "root";
$parola = "parola";
$numebd = "nume_bd";    // nume baza de date

// Conectarea la baza de date
$conn = mysql_connect($host, $utilizator, $parola);
if (!$conn) {
  echo 'Conectare nereusita la MySQL';
  exit;
}

// Selectarea bazei de date
if (!mysql_select_db($numebd, $conn)) {
  echo 'Baza de date nu a putut fi selectata deoarece : '. mysql_error();
  exit;
}

// Setarea pentru format UTF-8
$sql = "SET NAMES 'utf8'";
mysql_query($sql, $conn);

// Afla cate linii sunt in tabel (MODIFICATI 'nume_tb') din baza de date
$sql = "SELECT COUNT(*) FROM `nume_tb`";
$result = mysql_query($sql, $conn) or trigger_error(E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];  

// Stabileste numarul de linii din tabel afisate in pagina
$rowsperpage = 10;
// afla numarul total necesar de pagini
$totalpages = ceil($numrows / $rowsperpage);        // ceil face rotunjire la int. maxim

// Obtine pagina curenta sau seteaza default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
  // seteaza variabila ca int
  $currentpage = (int) $_GET['currentpage'];
} else {
  // pagina care este initial afisata (pagina default)
  $currentpage = 1;
}

// daca pagina curenta e mai mare decat total pagini...
if ($currentpage > $totalpages) {
  // seteaza pagina curenta la ultima pagina
  $currentpage = $totalpages;
}
// daca pagina curenta e mai mica decat prima pagina...
if ($currentpage < 1) {
  // seteaza pagina curenta la prima pagina
  $currentpage = 1;
} 

// lista cu pagini, in functie de pagina curenta
$offset = ($currentpage - 1) * $rowsperpage;  

// obtine datele din tabel (MODIFICATI 'nume_tb') din baza de date
$sql = "SELECT * FROM `nume_tb` LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error(E_USER_ERROR);  

// parcurgerea matricei cu datele obtinute
while ($list = mysql_fetch_assoc($result)) {
  // - MODIFICATI numele coloanelor tabelului ('id' si 'texte')
  // Stocheaza datele returnate de MySQL in variabile array pt. fiecare coloana
  $id[] = $list['id'];
  $text[] = $list['texte'];
}
mysql_close();  // Incheie conexiunea cu mysql

/*** Afisarea datelor obtinute ***/
// Parcurge variabilele array setate in bucla WHILE
for($i=0; $i<count($id); $i++) {
  // Aici puteti adauga cod HTML pentru aspectul grafic al afisarii
  echo $id[$i]. " - ". $text[$i]. "<br />";
}

/*** Construirea link-urilor pt. paginare ***/
// raza nr. link-uri din jurul celui curent
$range = 3;

// Link-uri inapoi, daca pagina curenta nu e prima
if ($currentpage > 1) {
  // arata << pt. link la prima pagina
  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'>&lt;&lt;</a> &nbsp; ";
  // obtine nr. pagina din urma
  $prevpage = $currentpage - 1;
  // arata < pt. link la o pagina in urma
  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'>&lt;</a> &nbsp;";
} 

// definirea link-urilor din raza paginii curente
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
  // daca e un nr. de pagina valid ...
  if (($x > 0) && ($x <= $totalpages)) {
     // daca nr. e pagina curenta ...
     if ($x == $currentpage) {
        // afiseaza nr. pagina fara a fi link
        echo " [<b>$x</b>] ";
     // daca nr. nu e pagina curenta ...
     } else {
        // il face link
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
     }
  }
}

// Daca pagina curenta nu e ultima, afiseaza link inainte si spre ultima pagina
if ($currentpage != $totalpages) {
  // obtine pagina urmatoare
  $nextpage = $currentpage + 1;
   // arata > pt. urmatoarea pagina
  echo "&nbsp; <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>&gt;</a> ";
  //  arata >> pt. ultima pagina
  echo " &nbsp; <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>&gt;&gt;</a> ";
}
?>


Please use the search function and the related questions widget before you post a new question next time.

The large LIMIT problem :

Beware of large LIMIT Using index to sort is efficient if you need first few rows, even if some extra filtering takes place so you need to scan more rows by index then requested by LIMIT. However if you're dealing with LIMIT query with large offset efficiency will suffer. LIMIT 1000,10 is likely to be way slower than LIMIT 0,10. It is true most users will not go further than 10 page in results, however Search Engine Bots may very well do so. I've seen bots looking at 200+ page in my projects. Also for many web sites failing to take care of this provides very easy task to launch a DOS attack - request page with some large number from few connections and it is enough. If you do not do anything else make sure you block requests with too large page numbers.

For some cases, for example if results are static it may make sense to precompute results so you can query them for positions. So instead of query with LIMIT 1000,10 you will have WHERE position between 1000 and 1009 which has same efficiency for any position (as long as it is indexed)

ORDER BY … LIMIT Performance Optimization

Related Question: Alphabetical pagination gets progressively slower as you page (MySQL)