Is this page too server intensive?

[ludachris]

I have a custom vB page on the site that pulls user data and sorts it. I have some stats turned on and this is what shows up for this page:

Page generated in 1.15693402 seconds (2.60% PHP - 97.40% MySQL) with 34 queries

Is that going to kill the db? The page loads quickly enough in the beginning.

I made some tweaks to this page a couple weeks ago, mainly adding a few sorting options, and it seems that after I made the page live, it results in the db slowing down until it crashes with too many connections.

[TigerC10]

That is kind of intensive, but it's not terrible. Unless you've got hundreds or thousands of people accessing the page at once, it shouldn't be a problem.

What you might be having a problem with is that you open a connection to the database, but you don't close it. Are you using vBulletin's database functions or are these your own?

[Reeve of shinra]

Maybe you can pull it once a day (via vb cron) and store it in the vb cache?

[ragtek]

Why put them to the vb cache(you mean datastore or?) when they are just needed on one site?
Caching idee is good, but i wouldn't put them to the datastore

[ludachris]

The cron job idea is interesting.

--------------- Added [DATE]1237771673[/DATE] at [TIME]1237771673[/TIME] ---------------

Originally Posted by TigerC10

That is kind of intensive, but it's not terrible. Unless you've got hundreds or thousands of people accessing the page at once, it shouldn't be a problem.

What you might be having a problem with is that you open a connection to the database, but you don't close it. Are you using vBulletin's database functions or are these your own?

It's a stand alone page using the suggested method of building a vb page outside of vb. How exactly do you use vb's db functions?

[TigerC10]

A thought occurred to me, are there search spiders on your board? Because if they're on that page, they could be the ones killing your site. Turn off the visibility to unregistered/guest users.

[Dismounted]

Could you show us the queries?

[ludachris]

Sure, here is where I think there could be issues:

/////// Get total user count //////
   $userscount=$db->query_first("SELECT COUNT(*) AS users
                                     FROM " . TABLE_PREFIX . "user AS user, " . TABLE_PREFIX . "userfield AS userfield
                                     WHERE userfield.field2=\"West Virginia\" AND userfield.field19!=\"\" AND userfield.field20!=\"\" AND userfield.field9!=\"\" AND userfield.field68!=\"\" AND userfield.field69!=\"\" AND userfield.field71!=\"\" AND userfield.field72!=\"\" 
                                     AND user.userid = userfield.userid");

/////// Set up pagination //////
   $numberpages = $userscount['users'] / $perpage;
   $numberpages = ceil($numberpages);

   if (!isset($pagenumber) or ($pagenumber < 1) or ($pagenumber > $numberpages))
    $pagenumber = 1;

    $pos = ($pagenumber-1) * $perpage;


/////// Get all users //////
	  $users=$db->query("SELECT *
                          FROM " . TABLE_PREFIX . "user AS user, " . TABLE_PREFIX . "userfield AS userfield
                          WHERE user.userid = userfield.userid
                          WHERE userfield.field2=\"West Virginia\" AND userfield.field19!=\"\" AND userfield.field20!=\"\" AND userfield.field9!=\"\" AND userfield.field68!=\"\" AND userfield.field69!=\"\" AND userfield.field71!=\"\" AND userfield.field72!=\"\"
                          ORDER BY userfield.field68 DESC
                          LIMIT $pos,$perpage");

   $counter=0;

Is that enough?

--------------- Added [DATE]1237902053[/DATE] at [TIME]1237902053[/TIME] ---------------

Did you need more info than what I posted?