optimisation of a code

[krystoffff]

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 this lead (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