Re: Query performance question

i'd suggest either using an MD5 hash or some sort of CRC

definitely a hash though


On Thu, 29 Mar 2001, Vijay Deval wrote:

> URL is a very large field.  If an extra field is created which gives a
> neumeric id to the url, run the query on the number, and then get the desired
> output
>
> Vijay
>
> Lukas Ertl wrote:
>
> > Hi,
> >
> > I'm having trouble with an obviously simple query that just doesn't
> > perform quite good IMO.
> >
> > I have two tables:
> >
> > httplog=# \d hits
> >                               Table "hits"
> >   Attribute  |   Type    |                   Modifier
> > -------------+-----------+-----------------------------------------------
> >  id          | integer   | not null default nextval('hits_id_seq'::text)
> >  page_id     | integer   | not null
> >  referrer_id | integer   | not null
> >  ip_addr     | inet      | not null
> >  time        | timestamp | not null
> >  domain_id   | integer   | not null
> > Index: hits_pkey
> >
> > httplog=# \d referrer
> >           Table "referrer"
> >  Attribute |     Type     | Modifier
> > -----------+--------------+----------
> >  id        | integer      |
> >  url       | varchar(300) |
> > Index: referrer_pkey
> >
> > These are part of an HTTP-log database. Table 'hits' has about 7000
> > rows, table 'referrer' has about 350 rows. Now I want to know what the top
> > ten referrers are, and I issue this query:
> >
> > SELECT count(*), url FROM hits, referrer WHERE referrer.id = referrer_id
> > GROUP BY url ORDER BY count DESC LIMIT 10;
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.
+1 651.645.9798 fax            Don't forget your BackPack!"
-----------------------------------------------------------------------------