optimisation of a code - Mailing list pgsql-sql

Hi all

I wrote a script in PHP with mysql (hum ... to be honnest, I wrote
this script under PostGreSQL but we recently migrated to mysql, so I
had to adapt my code to mysql ... sorry about that ... anyway, it is
the same kind of query, with subqueries !) but this is a very very
long script ...

I was wondering if there is a way to optimize this one, by doing some
JOIN (because the subqueries don't seem to be very powerful ...)

Here is the script :

To resume, it is a script that :- list all the leads available (1st query)- For each lead, find 3 members that can buy
thislead (2nd query)- For each member, buy the lead
 


<?

...

// FIRST QUERY
$q_avail = "select id, loan_type, affiliate_id,
borrower_credit_rating, prop_state, loan_amount, current_value, email,
nb_units, refinance_date, balance, down_payment, purchase_\
price, prop_address1, prop_address2, prop_city, prop_zip,
borrower_first_name, borrower_last_name, borrower_address1,
borrower_address2, home_phone, office_phone, co_borrower_fi\
rst_name, co_borrower_last_name, prop_is, co_borrower_credit_rating,
time, homeowner, date_creation, borrower_employer, ";
$q_avail .= " 1 as period, ";
$q_avail .= " $PRICE_SH_TIME1 as price, count(id) as nbsold ";
$q_avail .= " from lead LEFT JOIN purchase ON purchase.lead_id=lead.id
WHERE ";
$q_avail .= " unix_timestamp(now())-unix_timestamp(date_creation)<=
(24*3600*6) and ";
$q_avail .= " (loan_type='Refinance' or loan_type='Purchase' or
loan_type='Home Equity (AAA credit)') ";
//$q_avail .= " and (exclusive=NULL or exclusive=0) ";
$q_avail .= " group by id, loan_type, affiliate_id,
borrower_credit_rating, prop_state, loan_amount, current_value, email,
nb_units, refinance_date, balance, down_payment, purch\
ase_price, prop_address1, prop_address2, prop_city, prop_zip,
borrower_first_name, borrower_last_name, borrower_address1,
borrower_address2, home_phone, office_phone, co_borrowe\
r_first_name, co_borrower_last_name, prop_is,
co_borrower_credit_rating, time, homeowner, date_creation,
borrower_employer,period,price ";
$q_avail .= " having count(id) <3 ";

$r_avail = mysql_query($q_avail);
//echo $q_avail."<BR><BR><BR>";
       $today_midnight = strtotime(date('Y-m-d 00:00:00'));
       if ($AFF_FIXED_AMOUNTS)               $amount_fixed = $AFF_SHD_AMOUNT;       else               $amount_fixed =
$AFF_PERCENTAGE* .01 *
 
$PRICE_POINT_IN_DOLLARS;


while ($lead=mysql_fetch_assoc($r_avail))
{
   $n = $lead[period];   if ($lead[loan_type] == "Refinance") $type="refi";   else if ($lead[loan_type] == "Purchase")
$type="pur";  else $type = "homeq";   $field = $type."_t$n";   $price = $lead[price];   $id = $lead[id];   $aff_id =
$lead[affiliate_id];
   // SECOND QUERY       // find the members that fit all the required criterias   $q_members = "select member.id,
automated.delivery,member.email
 
from (automated INNER JOIN member ON member.id = automated.member_id)
";   $q_members .= " where activated=1 ";   $q_members .= " and website='$SITE_NAME'";   $q_members .= " and (select
count(*)from trans_member where
 
(unix_timestamp(now())-unix_timestamp(date)) <
(unix_timestamp(now())-'$today_midnight') and type='purchase' a\
nd comment LIKE '%automated%' ";   $q_members .= "   and member_id=member.id and comment LIKE
'%$type%') < max_$field ";   $q_members .= " and balance_in_points > $price ";   $q_members .= " and credit_ratings_t$n
LIKE
'%$lead[borrower_credit_rating]%' ";   $q_members .= " and states LIKE '%$lead[prop_state]%' ";   $q_members .= " and
ltv_t$n/100>= (cast($lead[loan_amount] as
 
unsigned) / cast($lead[current_value] as unsigned)) ";   $q_members .= " and amount_t$n < $lead[loan_amount] ";
$q_members.= " and $id NOT IN (select lead_id from purchase where
 
member_id=member.id) ";   $q_members .= " AND $aff_id NOT IN (select affiliate_locked_id
from affiliate_lockout where member_id=member.id) ";   $q_members .= " AND $id NOT IN (select lead_id from purchase
where
member_id IN (select member_id_to_exclude from member_exclusion where
member_id=member.id))";   $q_members .= " ORDER BY balance_in_points DESC";   $r_members = mysql_query($q_members);

$nbdispo = $NBPERSONS_SHARED - $lead[nbsold];
   while (($member=mysql_fetch_assoc($r_members)) && $nbdispo>0)       {
           BUY THE LEAD FOR THIS MEMBER           $nbdispo--;
       }


//}
} // END OF while ($lead=mysql_fetch_assoc($r_avail))


?>

Has anybody an idea ?
Thanks very much for your help
Krystoffff