Click here to get back home

select count in perl
 Home | NewsGroups | Search | About
Login Password
Register Now!
 comp.lang.perl.misc    Post an article   get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content
Subject Author Date
select count in perl avilella@gmail.com 06-09-2008
Posted by avilella@gmail.com on June 9, 2008, 10:56 am
Please log in for more thread options
 Hi,

I would like to know how I could move the select count I am doing in
mysql on the perl side:

mysql -hhost -uanonymous mydb -e "select count(status), status from
table group by status order by null"

to instead something like:

mysql -hhost -uanonymous mydb -e "select status from table order by
null" | perl "give me the count numbers"

Anyone?

Posted by bugbear on June 9, 2008, 11:00 am
Please log in for more thread options
 avilella@gmail.com wrote:
> Hi,
>
> I would like to know how I could move the select count I am doing in
> mysql on the perl side:
>
> mysql -hhost -uanonymous mydb -e "select count(status), status from
> table group by status order by null"
>
> to instead something like:
>
> mysql -hhost -uanonymous mydb -e "select status from table order by
> null" | perl "give me the count numbers"

If the table is large that's a major "lose"

BugBear

Posted by xhoster on June 9, 2008, 12:34 pm
Please log in for more thread options
> avilella@gmail.com wrote:
> > Hi,
> >
> > I would like to know how I could move the select count I am doing in
> > mysql on the perl side:
> >
> > mysql -hhost -uanonymous mydb -e "select count(status), status from
> > table group by status order by null"
> >
> > to instead something like:
> >
> > mysql -hhost -uanonymous mydb -e "select status from table order by
> > null" | perl "give me the count numbers"

If the number of distinct "status" is small, then

... | perl -lne '$h++; END {print "$_\t$h" foreach keys %h}'

There is a possibility that when used in this way, mysql will read the
entire table into memory before starting to write it out to the pipe. If
so, there is a way to tell it not to. I don't recall which is the default.
You might want to look into that.

If you use DBI, which may be a good idea, then I know the default is to
read the results into memory up front, which would probably be a bad idea
(because if the results were small enough to fit in memory, you'd probably
just stick with using the mysql group by in the first place.


> If the table is large that's a major "lose"

Or not. AFAICT, mysql's "group by" always uses a sort, even when a hash
would be much more efficient. Perl uses whatever you tell it to.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.

Posted by Marc Bissonnette on June 9, 2008, 11:18 am
Please log in for more thread options
keyboard. This was the result: news:01394da9-53af-4122-a3c9-7ba59cbbae80
@r66g2000hsg.googlegroups.com:

> Hi,
>
> I would like to know how I could move the select count I am doing in
> mysql on the perl side:
>
> mysql -hhost -uanonymous mydb -e "select count(status), status from
> table group by status order by null"
>
> to instead something like:
>
> mysql -hhost -uanonymous mydb -e "select status from table order by
> null" | perl "give me the count numbers"
>
> Anyone?

#!/usr/bin/perl
use CGI;
use URI::Escape;
$q = new CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
Define();

$query = "SELECT count(status) as totals, `status` FROM `table` GROUP BY
`status` ORDER BY null";
print "Status:\tTotals\n------------------\n\n";
my $dbh;
my $sth;
$dbh = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpass,{PrintError =>
1, RaiseError => 1});
$sth=$dbh->prepare($query);
$sth->execute();
$names = $sth->;
while (@row=$sth->fetchrow()) {
        $count=0;
        for (@row) {
                $fieldname=@$names[$count];
                $data=$row[$count];
                $data=uri_unescape($data);
                ++$count;
        }
        print "$data\t$data\n
}
$sth->finish;
$dbh->disconnect;
exit;

sub Define {
        $dbname='databasename';
        $dbuser='databaseuser';
        $dbpass='databasepassword';
}

--
Marc Bissonnette
Looking for a new ISP? http://www.canadianisp.com
Largest ISP comparison site across Canada.

Posted by Tad J McClellan on June 9, 2008, 8:46 pm
Please log in for more thread options
Marc Bissonnette <dragnet> wrote:

> #!/usr/bin/perl

use warnings;
use strict;

[ snip other code]


--
Tad McClellan
email: perl -le "print scalar reverse qq/moc.noitatibaher0cmdat/"

Similar ThreadsPosted
Re: iPod Smart Playlist, iTunes, skip count, play count, synchronization October 13, 2006, 10:48 pm
IO::Select::select() says no readable data even if there are August 24, 2006, 2:53 am
The select() (IO::Select) function has a limit? June 2, 2008, 5:24 am
awk - or how to select a column in perl December 14, 2005, 4:22 pm
Select / Case in perl August 7, 2007, 6:08 am
Perl DBI - Select across multiple Database March 10, 2006, 3:14 pm
MySQL & Perl: Using a Dynamic SELECT Statement November 14, 2007, 4:47 am
count lines,words,punctuations and characters in perl script November 11, 2006, 9:50 am
perl problem with select and non-blocking sysread from multiple pipes March 1, 2005, 4:33 am
problem starting perl script from cron environment - ps: don't know which terminal to select March 19, 2008, 8:24 am

Our other projects:

Art Dolls, Fairies and Mermaids - Sunnyfaces.net

Roy's Linux, Programming and Search Engines messages

1-Script XML SitemapXML Sitemap