[SQL] 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


";

$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
Krysto

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] How to optimize this query ?

[krystoffff]

OK, here is the final query without any subquery ...


--
SELECT L. * 
FROM lead L
LEFT JOIN purchase P ON ( L.id = P.lead_id ) 
LEFT JOIN member_exclusion M ON ( P.member_id = M.member_id_to_exclude
)
LEFT JOIN (

SELECT DISTINCT affiliate_locked_id
FROM affiliate_lockout
WHERE member_id = 21101
) AS A ON ( L.affiliate_id = A.affiliate_locked_id ) 
WHERE L.exclusive IS NULL OR (
L.exclusive = 0 AND L.nb_purchases < 3
) AND (
P.lead_id IS NULL OR P.lead_id <> 21101
) AND (
M.member_id IS NULL 
)
GROUP BY L.id
---

I've got the same result as before, so it should be correct ;)
By the way, the time seems to be the same (1.41s for the last form,
and 1.44s now) but I think it's because I don't have much stuff in
Member_exclusion (6 rows) so I will keep this query without subquery
...

Thanks, Franco !

PS : definitively, try to avoid the subqueries ! It's easy to program,
but very slow to execute !

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[SQL] Error message with a SQL function

[krystoffff]

Hi

I try to create the following SQL function:

CREATE FUNCTION update_affiliate(int,double precision,double
precision) RETURNS void AS ' UPDATE affiliate SET balance=balance +
$2, balance_in_points=balance_in_points + ( $2 / $3 ) WHERE id = $1; '
LANGUAGE 'sql';

but when I submit this query directly with PostgreSQL, I have the
following error message:

PostgreSQL said: ERROR: Unable to locate type oid 325664 in catalog 


I think the problem comes from the first parameter ($1) but I don't
understand because affiliate.id is indeed the primary key of the table
affiliate.

Has anybody an idea ?
Thanks
Krysto

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] How to optimize this query ?

[krystoffff]

Hi all

I have to optmize this query, because it takes a while to run (about
30s)

Here are the tables (with the keys):
 affiliate_lockout  (6 rows)  (member_id, affiliate_id)
 lead   (4490 rows)   (id, ...)
 member  (6 rows) (id, ...)
 member_exclusion (3 rows)(member_id, member_id_to_exclude)
 purchase (1 rows)(lead_id, member_id, ...)


Here is the query:
SELECT *
FROM lead
WHERE 
(exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND 

id NOT IN (

SELECT lead_id
FROM purchase
WHERE member_id = 21101
) AND affiliate_id NOT 
IN (

SELECT affiliate_locked_id
FROM affiliate_lockout
WHERE member_id = 21101
) AND id NOT 
IN (

SELECT lead_id
FROM purchase
INNER JOIN member_exclusion
WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
purchase.member_id = 21101
)


I wonder the problem is with the subqueries (which are apparently very
slow to run, according to what I read), but I can't figure how to
rewrite this query without any subquery ...

Maybe the problem comes from the index ... How would you create your
indexes to optimize this query ?

Could somebody help me ?
Thanks
krysto

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] How to optimize this query ?

[krystoffff]

I tried with some LEFT JOINS, which give me the possibility to keep
the information of the right table.

I have now the following query, which is 10 times faster !!! (from 16s
to 1.6s)
But I's like to remove the last subquery, to see if it faster ;)
Can somebody help me ?


-

SELECT lead. *
FROM lead
LEFT JOIN purchase ON ( lead.id = purchase.lead_id ) 
LEFT JOIN affiliate_lockout ON ( lead.affiliate_id =
affiliate_lockout.affiliate_locked_id )
WHERE (
exclusive IS NULL OR (
exclusive = 0 AND nb_purchases < 3
)
) AND id NOT 
IN (

SELECT lead_id
FROM purchase
INNER JOIN member_exclusion
WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
purchase.member_id = 21101
) AND (
affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS
NULL
) AND purchase.member_id <> 21101
GROUP BY lead.id

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Error message with a SQL function

[krystoffff]

Thanks

You were right, it was because of the return void
But I think in the documentation, I read somewhere that this kind of
return was allowed ... I must have mistaken ...

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] How to optimize this query ?

[krystoffff]

For the following query, I have a little problem ...
First, I have to rewrite several times the same query because the
alias are not recognised in the same query ( I got an error when I try
to reuse the alias "nb_bogus_leads", for instance). Do you have a way
to avoid this ? Because If I do so, the same query is calculated twice
...

Second problem, the most important :
The A.id should be for each result returned in A.*, and there should
be a join to calculate the query "nb_bogus_leads" (for instance) about
the A.id currently processed by the query.
But it seems that this join doesn't work, because I have the same
"nb_bogus_leads" and same "nb_leads_submitted" for each A.id returned
(they should be different !)

How can you make this query work ?
Thanks


SELECT A. * , (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM request
INNER JOIN lead ON ( lead_id = lead.id ) 
WHERE allowed = 1 AND lead.affiliate_id = A.id
) AS nb_bogus_leads, (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id
) AS nb_leads_submitted, (

CASE WHEN (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id
) <> 0
THEN (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM request
INNER JOIN lead ON ( lead_id = lead.id ) 
WHERE allowed = 1 AND lead.affiliate_id = A.id
) / ( 
SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id ) * 100
WHEN (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id
) = 0
THEN 0 
END 
) AS percentage_bogus_leads
FROM affiliate A
WHERE website = 'dev'

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly