Page 1 of 1

php/mysql display results based on division of 30%

Posted: Thu Nov 02, 2006 4:08 am
by blacksnday
I have recently acquired the need to only display certain result sets based on a 30% conditional.
More namely,
I only want to display Affiliate Links that send me no less then 30% of the referrals that I have sent them.

My current SQL table that holds IN/OUT stats for referral links is basically this:

LINKS_AFF
==========
id | active | in | out

If the IN field(the amount of links they have sent to me)
is 50
and the OUT field(the amount of links I have sent them)
is 100
(in the above example, the in-to-out ratio is 50%, in which case I would not want to display their link
until their IN total was no less then 70)

How to I create the mySQL/php to not display that link until they send no less
the 30% of what I have sent them?

Posted: Thu Nov 02, 2006 4:15 am
by volka
WHERE `in`/`out`> 0.7

but then no table index will help mysql to find the records, it has to scan them all.

Posted: Thu Nov 02, 2006 4:39 am
by blacksnday
volka wrote:WHERE `in`/`out`> 0.7

but then no table index will help mysql to find the records, it has to scan them all.
would that also include if you index other fields besides in/out in the table that are within the current WHERE or AND Clause

if so, is there any other way to achieve what I am attempting?

Posted: Thu Nov 02, 2006 4:49 am
by volka
I'd try WHERE `in`/`out`> 0.7
If it gets too slow you can still add an extra field that holds the value of in/out (or only flag wether in/out is greater x)
And if it still is slow (e.g. because the fields are updated too often) you can use a cronjob to only update the records e.g. each 15 minutes or each hour ...

Posted: Thu Nov 02, 2006 4:55 am
by blacksnday
volka wrote:I'd try WHERE `in`/`out`> 0.7
If it gets too slow you can still add an extra field that holds the value of in/out.
And if it still is slow (e.g. because the fields are updated too often) you can use a cronjob to only update the records e.g. each 15 minutes or each hour ...
what works is:

Code: Select all

income/count > 0.7
and I index on three fields:
(which werent shown in the example for ease of confusion?)
INDEX on:
id (auto-increment)
clientid
paid

which the indexes above are how my WHERE clause selects
(namely from paid, unless in member center viewing the links you own
then it would be from clientid)
and the rest of the mysql query would be from AND clauses such as:

Code: Select all

WHERE paid = '1'
AND income/count > 0.7
etc.....

based on that, would using the

Code: Select all

AND income/count > 0.7
affect my WHERE indexes?

Posted: Thu Nov 02, 2006 5:00 am
by volka
Yes, there is no index that helps mysql to filter the records matching
AND income/count > 0.7
So this part mysql has to do record by record.
You have an index for the field paid, therefore mysql does not have to scan all records for paid='1'.
But for the remaining records (having paid=1) it has to perform in/out for each record and then compare it to 0.7.

Simply try it - it's not that much of a change, is it? Maybe you will not even notice the difference.

Posted: Thu Nov 02, 2006 5:02 am
by blacksnday
volka wrote:Yes, there is no index that helps mysql to filter the records matching
AND income/count > 0.7
So this part mysql has to do record by record.
You have an index for the field paid, therefore mysql does not have to scan all records for paid='1'.
But for the remaining records (having paid=1) it has to perform in/out for each record and then compare it to 0.7.
Thanks!
I think i will go ahead and use the solution then.
Afterall, as my current query stands, after Paid it needs to search all.
So not much of a diff adding the percentage display as it stands now.