Re: Where to count

The COUNT tells me how many times a particular combination of source IP,
destination IP, and service appears in the logs. The ORDER BY puts it in
decending order, and the LIMIT only shows me the top 25/50/etc. entries.

It works like I want it to - I'm just checking to see if this is the most
efficient way of doing things. Like, should I make an index on something
to accomplish this goal.

--Wade

On Wed, 10 Oct 2001, Mitch Vincent wrote:

> If you use LIMIT, count(*) is going to return that limit even if there are
> more than the specified limit.
>
> Why are you going a LIMIT here if it's the count you're looking for?
>
>
> ----- Original Message -----
> From: "H. Wade Minter" <minter(at)lunenburg(dot)org>
> To: <pgsql-general(at)postgresql(dot)org>
> Sent: Wednesday, October 10, 2001 3:46 PM
> Subject: [GENERAL] Where to count
>
>
> > I'm running a DB query on a database of firewall log entries (right now
> > around 700k rows). What I want to do is pull out some common entries, as
> > well as the number of times that they occur in the table.
> >
> > Right now, I'm doing a query like:
> >
> > select source,destination,service,count(*) FROM logs WHERE action='$type'
> > GROUP BY source,destination,service ORDER BY count DESC LIMIT $num;
> >
> > This is a little more advanced than I'm used to doing, so I'm wondering if
> > that query is the best way to get that data, or if there's another way of
> > doing it.
> >
> > Thanks,
> > Wade
> >
> > --
> > Do your part in the fight against injustice.
> > Free Dmitry Sklyarov! http://www.freesklyarov.org/
> > Fight the DMCA! http://www.anti-dmca.org/
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Do your part in the fight against injustice.
Free Dmitry Sklyarov! http://www.freesklyarov.org/
Fight the DMCA! http://www.anti-dmca.org/