I dont like to remove the RANK field onless there is no other solution
becouse this Rating system going to be used by a community of people to
messure the performance of their web sites compaired to others the RANK must
be there.
I am thinking about calculating RANK once a more longer interval like once
a 7 days.
This method of updating will be bit faster if there is a way i can write
this as a function in the DBMS "Like a PL/SQL Function in Oracle" is this
possible in MySQL
________________________________________
Rakhitha Karunarathne
Web Master
www.Ad-Man.tk - Free Unlimited Banner Rotators
________________________________________
----- Original Message -----
From: "Dani Pardo" <[EMAIL PROTECTED]>
To: "LRMK" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, May 21, 2004 1:06 PM
Subject: Re: A MySQL Question
> On Fri, 21 May 2004, LRMK wrote:
>
> > Probably this is not relavent to this mailing list... But any way I ll
ask..
> >
> > I am developing a web site traffic rating system. using a combination og
> > perl and java (PERL in serverside, Java Applets in cliend side - My
attempt
> > to create dynamic images was not that successfull so I use a java applet
to
> > view the rating info on the site)
> >
> > Here is my question The rating system is now working but every day I
have to
> > run a hevy SQL statement to recalculate the traffic ranks of web sites
> >
> > Something similer to this
> >
> > $DBC is the connection to the mysql database
> >
> >
> > my $q = $DBC->prepare("SELECT domainname, (viewcount/(($now -
> > startedTime)/86400)) AS views_per_day " .
> > "FROM site WHERE
startedTime<($now -
> > 48hours) " . # I do not give ranks for the sites registered withing
> > last 48 hours
> > "ORDER BY views_per_day DESC")
> >
> > my $rank = 0;
> >
> > $q->execute();
> >
> > while (my $ref = $q->fetchrow_hashref()){
> > $rank++;
> > my %hash = %{$ref};
> > $DBC->do("UPDATE site SET rank = $rank WHERE domainname=" .
> > $DBC->quote($hash{'domainname'}));
> >
> > }
> >
> > $q->finish();
> >
> > $DBC->do("COMMIT");
> >
> >
> >
> >
> > the problem is if there are 1500 qualified records in the table there
going
> > to 1501 queries (there going to be lots mor records than 1500 in the
real
> > system)
> >
> > Question 1 ) Is there a more efficiant way of doing this, atleast to
reduce
> > the number of queries?
>
> I guess you are updating filed RANK, so that the domain that has more
> visits per day gets value 1, the second gets value 2, etc..
> Can you just omit this fileld? So you don't need to the script that
> recalculates :)
> I mean, can you forget about the rank filed and only work with
> (viewcount/(($now - startedTime)/86400)) (wiews per day)?
>
> ---
> Dani Pardo, [EMAIL PROTECTED]
> Enplater S.A
>
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
