X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id D4BD8D1C9BA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 11 Aug 2003 22:48:53 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 53720-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 11 Aug 2003 19:47:25 -0300 (ADT)
Received: from news.hub.org (news.hub.org [64.117.224.194])
	by svr1.postgresql.org (Postfix) with ESMTP id 06F3DD1C4DF
	for <pgsql-sql@postgresql.org>; Mon, 11 Aug 2003 19:48:43 -0300 (ADT)
Received: from news.hub.org (news.hub.org [64.117.224.194])
	by news.hub.org (8.12.9/8.12.9) with ESMTP id h7BMmgek061619
	for <pgsql-sql@postgresql.org>; Mon, 11 Aug 2003 22:48:42 GMT
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.12.9/8.12.9/Submit) id h7BMJm0K054816
	for pgsql-sql@postgresql.org; Mon, 11 Aug 2003 22:19:48 GMT
From: proghome@silesky.com (krystoffff)
X-Newsgroups: comp.databases.postgresql.sql
Subject: optimisation of a code
Date: 11 Aug 2003 15:19:08 -0700
Organization: http://groups.google.com/
Lines: 126
Message-ID: <85898f7e.0308111419.2d770a9a@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Complaints-To: groups-abuse@google.com
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/113
X-Sequence-Number: 14729

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