Lrmk wrote:
>
> 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)
> 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 know if I use Oracle db i can make a PL/SQL procedure but my web host only
> give MySql
>
> Question 2 ) Can I make functions with mySQL if yes where can I find a
> tutorial?
Hi.
I would keep the rank values in a separate table with just two columns: the
domain name and its rank. You could obviously pretend that it's part of the
main table by setting up a view. The all you need to do is
INSERT INTO rank (domainname, rank)
VALUES ($domainname, $rank)
ON DUPLICATE KEY UPDATE rank = $rank
As for the functions,take a look here:
http://dev.mysql.com/doc/mysql/en/CREATE_FUNCTION.html
HTH,
Rob
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
