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>