PERL Beginners - A MySQL Question

Author

A MySQL Question

Lrmk

2004-05-21, 12:30 am

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 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?

________________________________________

Rakhitha Karunarathne
Web Master
www.Ad-Man.tk - Free Unlimited Banner Rotators
________________________________________

Dani Pardo

2004-05-21, 4:30 am

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, dani@enplater.com
Enplater S.A

Lrmk

2004-05-21, 4:30 am

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" <dani@enplater.com>
To: "LRMK" <perl@ad-man.sferahost.com>
Cc: <beginners@perl.org>
Sent: Friday, May 21, 2004 1:06 PM
Subject: Re: A MySQL Question

> On Fri, 21 May 2004, LRMK wrote:
>
ask..[color=darkred]
attempt[color=darkred]
to[color=darkred]
have to[color=darkred]
startedTime<($now -[color=darkred]
going[color=darkred]
real[color=darkred]
reduce[color=darkred]
>
> 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, dani@enplater.com
> Enplater S.A
>
>

Dani Pardo

2004-05-21, 5:30 am

On Fri, 21 May 2004, LRMK wrote:

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

You can use CREATE FUNCTION. But these functions are created in C (i
guess), and are compiled in a dynamic library (.so) into mysql.

---
Dani Pardo, dani@enplater.com
Enplater S.A

Rob Dixon

2004-05-21, 11:33 am

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

Lrmk

2004-05-23, 11:30 pm

Thanx this will work . My web host has PostgreSQL installed I can get a DB
from there.

By the way is PostgreSQL free and if yes where can I get a copy to practise
myself.
________________________________________

Rakhitha Karunarathne
Web Master
www.Ad-Man.tk - Free Unlimited Banner Rotators
________________________________________

----- Original Message -----
From: "Randal L. Schwartz" <merlyn@stonehenge.com>
To: "LRMK" <perl@ad-man.sferahost.com>
Sent: Friday, May 21, 2004 9:24 PM
Subject: Re: A MySQL Question

> If you have an actual database (not MySQL, but something modern like
> PostgreSQL or Oracle), you can solve this with a simple subselect and
> self join. Not the most efficient, but probably more efficient than a
> lot of roundtrips to the Perl side.
>
> For mytable which has "rank" and "score" where you want "rank" to reflect
> ranking where "score" is highest, use:
>
> UPDATE mytable
> SET rank = (
> SELECT 1+count(*)
> FROM mytable AS b
> WHERE b.score > mytable.score
> );
>
> This has the

feature that ties are automatically assigned the
> same value. For example:
>
> rank | score
> ------+-------
> 6 | 10
> 5 | 20
> 3 | 30
> 3 | 30
> 2 | 40
> 1 | 50
>
> --
> Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777
0095
> <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
> See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl
training!
>
>

Daniel Staal

2004-05-24, 11:32 am

--As of Monday, May 24, 2004 8:43 AM +0600, LRMK is alleged to have said:

> By the way is PostgreSQL free and if yes where can I get a copy to
> practise myself.

--As for the rest, it is mine.

Yes: <http://www.postgresql.org/>

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

Sponsored Links