The number of queries can cause overhead due to the round-trip delay, but this will depend on a number of factors.

• Location of the database server. Same machine or different
• Database load
• Waiting for table/row access when the data is locked for writing.

• Expensive join clauses
• Acquiring table/row locks
• Amount of data being transferred

But honestly the best way to see which approach works best for you is to benchmark it. Optimizing before you identify the bottlenecks can absorb a huge amount of your development time with very little return on the investment.

I'm very fond of using Benchmark to determine which algorithm runs fastest, but this is only part of the problem in your case. You really need to see how well it performs when the load is as high as you fear it might become.

Try changing this sequence of events:

request -> database query -> response

daemon  -> database query -> cached
request -> cached value   -> response

As far as using prepare_cache, if you are executing the same statment multiple times with the same $sth, it should help, but are you really re-using the same $sth between requests?

        my ($id, $username);
    my $sth = $dbh->prepare_cached(<<SQL);
Select id, username From members
SQL
        $sth->execute();
        $sth->bind_columns(undef, \$id, \$username);



#category queries
        my ($count);
    my $sthx = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND read = '2'
SQL
        my ($count_v);
    my $sthv = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND read = '3'
SQL

        my ($count_c);
    my $sthc = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND read = '4'
SQL

        my ($count_e);
    my $sthe = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND pending = 'p'
SQL
        my ($count_m);
    my $sthm = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND requests > 5
SQL

        my ($count_w);
    my $sthw = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND votes > 0
SQL


#

        while $sth->fetch() {
$sthx->execute($id);
$sthx->bind_columns(undef, \$count);
$sthx->fetch();
    print "<td>$count</td>";

$sthv->execute($id);
$sthv->bind_columns(undef, \$count_v);
$sthv->fetch();
    print "<td>$count_v</td>";

#etc...etc.. until all sth's are executed.
#going to add a limit to the # of members to fetch, perhaps #100 membe
+rs a page....if thats the case:
#
#1 select query to fetch all 100 member id's.
#6 selects for each member, x 100 members so 600 queries to #print out
+ the table full of values.
#total to 601 select queries
       }


<br><br>
Thanks, Bobby
[download]

This set of queries:

SELECT COUNT(*) From Results WHERE user_id = ? AND read = '2'
SELECT COUNT(*) From Results WHERE user_id = ? AND read = '3'
SELECT COUNT(*) From Results WHERE user_id = ? AND read = '4'
[download]

Could be replaced with something like this:

SELECT read, COUNT(*) From Results WHERE user_id = ? AND read in (2,3,
+4) GROUP BY read
[download]

Assuming there are entries for those three values you would get back 3 rows, where column 0 is the 'read' value, and column 1 is the number that had the value.

UPDATE Fixed the query, as TrekNoid pointed out that I omitted the group by clause. Apparently I neglected to select that portion before hitting copy.

Wouldn't you need a 'group by' in there?

SELECT read, COUNT(*)
  FROM Results
 WHERE user_id = ?
   AND read in ('2','3','4')
 GROUP BY read
[download]

I know Oracle really well, so it's possible that I'm just being Oracle-centric :)

Trek

Try to avoid nested queries by storing the results from the first query in a hash or array. Then loop over that structure to do more queries. You can also reuse those statement handles and variables. This example avoids the nesting and might be further improved with joins, or a more normalized database.

my $sth = $dbh->prepare_cached(<<SQL);
Select id, username From members
SQL

$sth->execute();
$sth->bind_columns(\my($id,$username));

my $user;

while ($sth->fetch) {
  $user->{$id} = $username
}

for my $id (keys %$user) {
  $sth = $dbh->prepare_cached(q{SELECT COUNT(*) From Results WHERE use
+r_id = ? AND read = ?});
  $sth->execute($id,2);
  $sth->bind_columns(\my($count));
  $sth->fetch;
  print $count;

  $sth = $dbh->prepare_cached(q{SELECT COUNT(*) From Results WHERE use
+r_id = ? AND read = ?});
  $sth->execute($id,3);
  $sth->bind_columns(\$count);
  $sth->fetch;
  print $count;
}
[download]