optimisation of a code

Поиск
Список
Период
Сортировка
Искать

optimisation of a code

От:
proghome@silesky.com (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




"; $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
FAQ