mysql querys slowing down page load time

Can I make one query get 6 values, instead of 6 queries get 1 each?

[aeae]

Hello,

I'm trying to make my site load faster, and it looks like the thing thats slowing it down the most right now is how it looks up some information in my MySQL database.

I show one of three different content blocks based upon the user's past views and clicks. In order to make the decision, my script needs to look into my database to see the number and types of views and clicks the user has had.

I'm wondering if there might be a more efficeint way of getting all these variables. Maybe I could set up a query to grab six variables, rather than six queries that grab one each, for example.

Here's my code:

$clicks_60_min=mysql_num_rows(mysql_query("SELECT cookie FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-3600 AND clicked>0"));

if($clicks_60_min>=2){
$a_clicks_30_min=mysql_result(mysql_query("SELECT SUM(clicked) FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-1800 AND type='a'"),0);
$a_clicks_60_min=mysql_result(mysql_query("SELECT SUM(clicked) FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-3600 AND type='a'"),0);
$b_clicks_30_min=mysql_result(mysql_query("SELECT SUM(clicked) FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-1800 AND type='b'"),0);
$b_clicks_60_min=mysql_result(mysql_query("SELECT SUM(clicked) FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-3600 AND type='b'"),0);
$c_clicks_30_min=mysql_result(mysql_query("SELECT SUM(clicked) FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-1800 AND type='c'"),0);
$c_clicks_60_min=mysql_result(mysql_query("SELECT SUM(clicked) FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-3600 AND type='c'"),0);
}
else $views_180_min=mysql_num_rows(mysql_query("SELECT cookie FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-10800"));

if($views_180_min>=10){
$a_views_30_min=mysql_num_rows(mysql_query("SELECT cookie FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-1800 AND type='a'"));
$a_views_180_min=mysql_num_rows(mysql_query("SELECT cookie FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-10800 AND type='a'"));
$b_views_30_min=mysql_num_rows(mysql_query("SELECT cookie FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-1800 AND type='b'"));
$b_views_180_min=mysql_num_rows(mysql_query("SELECT cookie FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-10800 AND type='b'"));
$c_views_30_min=mysql_num_rows(mysql_query("SELECT cookie FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-1800 AND type='c'"));
$c_views_180_min=mysql_num_rows(mysql_query("SELECT cookie FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND first_shown>=NOW()-10800 AND type='c'"));
}

$current_a_ban=mysql_num_rows(mysql_query("SELECT cookie FROM ad_purgatory WHERE ip='$ip' AND ban='a' AND ends>=NOW()"));
$current_b_ban=mysql_num_rows(mysql_query("SELECT cookie FROM ad_purgatory WHERE ip='$ip' AND ban='b' AND ends>=NOW()"));

Thanks for any help

[8kobe]

First, check your indexes. If you select more then insert then add a few. Second, use count instead of mysql_num_rows. 3rd use an EXPLAIN to figure out the time of your queries to fix the slow owes. You can also use PHPMYADMIN to figure out the time it takes to run. Those combined should at least lead you in the right direction.

[bmcgee]

I don't know mysql syntax so this will only be directional. But look for something like CASE WHEN and the do something like:

SELECT SUM(CASE WHEN first_shown>=NOW()-1800 THEN clicked ELSE 0 END) As Clicked1800, SUM(CASE WHEN first_shown>=NOW()-3600 THEN clicked ELSE 0 END) As Clicked3600 FROM ads WHERE (ip='$ip' OR cookie='$cookie') AND type='a'

This is an example of combining two into one query, follow it to select all 6 sums in one query.