PHP & Postgres pagination

[chrisw09]

I'm not sure it this is a post for PHP or the database forum, but since my question deals with php functions, i put it here.

In the past when i would page results i do the following:

1) get the count of my full resultset
2) calculate limit and offset
3) query db again with limit and offset
4) display results

This works fine, and how it's done.

But, lets say the query to generate the resultset is very costly (multiple joins, etc.) that takes a couple of seconds to finish. While digging through the PHP functions for postgres, i came across the fact that you can pass an int to pg_fetch_row to specifiy the offset in the result resource. The same offset can be accomplished by using pg_result_seek.

So, my question is, which would be better if you have an expensive query?

1) exec 2 queries, 1 to find results count, and 1 to get the resultset based on the first query
or
2) exec 1 query, use pg_num_rows() for page/offset calcuation, and pass an offset value to the pg_fetch_row function to get the rows you want.

Thanks for your feedback.

[petr.pavel]

Hi Chris,
to know for sure you'll probably have to do some measurement. I recommend reading about ANALYZE (to do the measurements right is a kind of a science).

For basic PHP measurement you can use $start = time() at then $length = time()-$start. But for real work I recommend extension XDebug.

Basically, you balance between the cost of the query and the cost of:
- transferring the result from db server to apache/php
- and browsing the result

So if the result is really large then you may be better of by running the query twice. Postgres caches a lot of things so running the query for the second time may not be as long as for the first time. I guess you did your homework about tuning up the query in the first place.

However, query results are rarely so big so there's a great chance you'll get better results with running the query just once. If you don't hit into memory limit :-)

I guess my reply comes a bit too late so this is for the sake of others having the same dilemma.

[forzadraco]

<?php
/* paging_dinamis.php */

require_once "connection.inc.php";

echo "<form action=\"$PHP_SELF\" method=\"GET\">";
echo "<b>Number of Paging :</b>
<select name='batas'>
<option value='3'>3
<option value='5'>5
<option value='10'>10
<option value='10'>15
</select>&nbsp;";
echo "<input type=submit value='submit'>";
echo "</form>";

$flname=basename($PHP_SELF);

$res = mysql_query("SELECT * FROM table_name ORDER BY id");

$jml = @mysql_num_rows($res);
if ($jml == 0) {
echo "<font color=red>
<b>Ooops.... Data not found</b></font>";
exit;
}

// Initialization default value for paging
if (isset($_GET["batas"])) {
$batas = $_GET["batas"];
} else {
$batas = 3;
}

if (($jml % $batas) == 0) {
$jmlpage=(int)($jml/$batas);
} else {
$jmlpage=((int)$jml/$batas)+1;
}

// Inisialisasi variabel page
if (isset($_GET["page"])) {
$page = $_GET["page"];
} else {
$page = 1;
}

if ($page>$jmlpage) {
$page = $jmlpage;
}

while ($rows = mysql_fetch_array($res)) {
$arrdata[] = $rows;
}

$end = ($page*$batas)-1;
$start= $end-($batas-1);
if ($end > $jml) {
$end = $jml-1;
}

for ($i=$start; $i<=$end; $i++) {
$arr[] = $arrdata[$i];
}
echo "<table width=450 style='border:1pt solid #666666;'>";
foreach ($arr as $row) {
echo "<tr><td width=100>Nama</td>
<td width=10>:</td><td>$row[1]</td></tr>";
echo "<tr><td>Email</td><td>:</td><td>
<a href='mailtorow[2]'>$row[2]</a></td></tr>";
echo "<tr><td>Komentar</td>
<td>:</td><td>$row[3]</td></tr>";
echo "<tr><td>&nbsp;</td></tr>";
}

echo "</table> <br>";

// Manage paging navigation
for ($n=1; $n<=$jmlpage; $n++) {
$b = $page + 1;
if ($n != $page) {
echo "&nbsp;<a href='$flname?page=$n&batas=$batas'>
Hal $n</a>&nbsp;";
} else {
echo "<font color='#999999'><b>Hal $n </b></font>";
}
}

// Next navigation paging
if (($n != $page) && ($n > $b)) {
echo "&nbsp;<a href='$flname?page=$b&batas=$batas'>
Next</a>";
}
?>