<!-- select comments from db_table where user=12345 limit 125,10 order by lastmod desc; -->

Then paste that on a command line, see how long it takes to run. If it's under a second, it's not likely the problem. If it takes time to query, the problem is your DB is not optimized. This can be anything from poor indexing to limitations on your usage or even an overloaded server.

If it runs fast on the command line, it could be networking issues or just an overloaded server.

The next is really left field because it's how your script should be working, and probably is, but just in case because I have seen this. Note my bolded text above. This indexes directly to the next page of results and *should* be relatively fast. If your selects are not using limit and are parsing through the entire table, just grabbing what's needed within the script, this would take forever and needs fixing.

Last is the nature of your environment: is it shared hosting, possibly overloaded? Do you have limitations on your hosting account that are slowing you down?

When I get in these situations, I take the specific code that's giving the grief and put it in a test script to isolate it. Usually this leads to an understanding of exactly what's going on and it can be fixed one way or the other.

use Benchmark;
$t0 = new Benchmark;
# ... your code here ...
$t1 = new Benchmark;
$td = timediff($t1, $t0);
print "the code took:",timestr($td),"\n";

I'm currently on a semi-dedicated server now which actually runs pretty fast. I don't have any trouble on the other areas of my site. My site was built by various different programmers so some areas seem to work better then others which makes me think its coding issues?

It doesn't look to me like a limit is being using:

my $sth7 = $dbh->prepare("select * from comments where member=$userid"); 

If I were to use a limit, wouldn't it ONLY show that # of comments? I need to show all the users comments... would that still be able to work?

Thanks again for your time, I appreciate it!

If I were to use a limit, wouldn't it ONLY show that # of comments? I need to show all the users comments....

The way it should work is you select only what you need for this page view. So if you view 10 per page, the first time,

select * from comments where user=$userid limit 0,10;

0 is not necessary the first time but when you loop through to create links, it's useful. Your links below the first 10 query the next section, and so on.

$per_page=10;
select count(*) from comments where userid=$userid;
....
$total_records=$sth->fetchrow_array;

for $i (0..($total_records+=$per_page)) {
$end=$i+$per_page;
$links .= "<a href="script?start=$i&amp;end=$per_page">$i - $end</a>";
}

(Actually this isn't correct, you want the link to say 1-10 not 0-10, for example only)

So by passing your current place in the records, you start at a different place with each page:

select * from comments where user=$userid limit 11,20;
select * from comments where user=$userid limit 21,30;
select * from comments where user=$userid limit 31,40;

---------------------------

Something else came to mind that has nothing to do with your script.

Once it loads, if you go to the next page . . .

Tell me, how many links are at the bottom of the page when you do this? I learned a lesson early on about creating footlinks from huge databases. :-) Is possible there are too many links, choking the browser?

[edited by: rocknbil at 5:07 am (utc) on Oct. 20, 2008]

Thanks rocknbil... you are so helpful :)
The account I usually test the load time has almost 2000 comments so at 10 per page, the "pagination" list of numbers is huge (shows page 1 to 200). You think this is the problem? Thats what I had initially thought... seems to me that the script has to query the entire database in order to show all these pages, no?

You know, you may be on to something because now that I think of it, it takes 5 minutes to load the initial page but even when you go to page 2 or 3... it still takes 3-4 minutes which I'm assuming is because it has to display all those page links each time you go to a new page.

I wanted to implement this other pagination that I found on the net - It allows you to have a nice digg-style pagination system. Do you think this will cause the same issues as I have now? What about if I were to implement that with the code idea you posted above? Would the two work together?

Thanks again, I hope I'm not taking up too much of your time :(

[edited by: phranque at 8:01 am (utc) on Oct. 20, 2008]
[edit reason] No urls, please. See TOS [webmasterworld.com] [/edit]

....2000 comments so at 10 per page, the "pagination" list of numbers is huge (shows page 1 to 200).

No, 200 links should be fine - UNLESS each link is a query string that is **really** long. Even then, doubtful. But as your database grows, I guarantee you, this WILL be a problem.

... seems to me that the script has to query the entire database in order to show all these pages, no?

See previous examples - absolutely not.

You do a count to see how many records there are.

You do a limit on your selects to select only the data needed for the current page.

You have a routine that, based on the total records you counted, creates the links WITHOUT reading each record.

. . . I wanted to implement this other pagination that I found on the net . . .

Don't know. :-) This is actually kinda typical of project cobbled together from things found free on the net. Any one of them could have problems.

Not sure if there is an index Mark, I will have to look into that. My knowledge on all this is very limited so I don't always understand what the script is doing or how the database is setup.

[edited by: phranque at 11:16 pm (utc) on Oct. 20, 2008]