Paging of Large db advise needed

[StefanRSA]

I have a DB with 400 000+ entries and am trying to split it into 10 entries per page...

 

My usual paging for smaller DB rows are working fine with no loading issues. Just with the large table (with joins) it bacomes a very long page load.
I am not sure what should be done to improve page load....

 

The script I have for paging works as follow:

$query2 = mysql_query("SELECT COUNT( * )
          FROM table_in 
          WHERE table_in.scat='$blcid' $wprov $wtown");
list($nrResults) = mysql_fetch_row($query2);


//$nrResults=mysql_num_rows($result2);
    IF (($nrResults%$limit)<>0) {
        $pmax=floor($nrResults/$limit)+1;  // Divide to total result by the number of rows needed per page
// to display per page($limit) and create a Max page
    } ELSE {
        $pmax=floor($nrResults/$limit);
    }
    
$pagenumber=(($pages-1)*$limit);
$query=   "SELECT field, field, field, field 
          FROM table_in  
          JOIN s_tow ON s_tow.tId=table_in.tow
          JOIN s_reg ON s_reg.rId=table_in.ar
          JOIN s_pro ON s_pro.pId=table_in.pro
          JOIN bt ON bt.id=table_in.lid
          WHERE table_in.sct='$blcid' $wprov $wtown
          GROUP BY table.lid ORDER BY date $nav limit $pagenumber, $limit";
$allads=mysql_query($query) or die(mysql_error());

This works fine if I have up to 500 rows.... But cause havoc with 400 000 rows....
What should I do?

[timcadieux]

I had this same problem recently but I was able to incorporate this Class and it works flawlessly,

 

http://www.catchmyfame.com/2011/10/23/php-pagination-class-updated-version-2/

[Barand]

400,000 isn't overly large. Queries with joins and several million rows can execute in well under a second.

 

Do you have indexes on the columns that you are searching on, such as "sct"?

[StefanRSA]

timcadieux - I just tried version one and did not really see an improvement... Will try V2

 

Barand - Yes, all related columns are indexed...

[Barand]

 

Barand - Yes, all related columns are indexed...

 

That wasn't the question

[StefanRSA]

Sorry Barand,

 

Yes, the "sct" column is indexed.

[StefanRSA]

Thanks for the help... My join order was wrong...