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?
php/mysql display results based on division of 30%
Moderator: General Moderators
- blacksnday
- Forum Contributor
- Posts: 252
- Joined: Sat Jul 30, 2005 6:11 am
- Location: bfe Ohio :(
- blacksnday
- Forum Contributor
- Posts: 252
- Joined: Sat Jul 30, 2005 6:11 am
- Location: bfe Ohio :(
would that also include if you index other fields besides in/out in the table that are within the current WHERE or AND Clausevolka wrote:WHERE `in`/`out`> 0.7
but then no table index will help mysql to find the records, it has to scan them all.
if so, is there any other way to achieve what I am attempting?
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 ...
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 ...
- blacksnday
- Forum Contributor
- Posts: 252
- Joined: Sat Jul 30, 2005 6:11 am
- Location: bfe Ohio :(
what works is: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 ...
Code: Select all
income/count > 0.7(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.7based on that, would using the
Code: Select all
AND income/count > 0.7Yes, there is no index that helps mysql to filter the records matching
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.
So this part mysql has to do record by record.AND income/count > 0.7
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.
- blacksnday
- Forum Contributor
- Posts: 252
- Joined: Sat Jul 30, 2005 6:11 am
- Location: bfe Ohio :(
Thanks!volka wrote:Yes, there is no index that helps mysql to filter the records matchingSo this part mysql has to do record by record.AND income/count > 0.7
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.
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.