From: | "D(dot) Duccini" <duccini(at)backpack(dot)com> |
---|---|
To: | Vijay Deval <deval(at)giaspn01(dot)vsnl(dot)net(dot)in> |
Cc: | Lukas Ertl <l(dot)ertl(at)univie(dot)ac(dot)at>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Query performance question |
Date: | 2001-03-29 10:51:08 |
Message-ID: | Pine.GSO.4.03.10103290450050.27298-100000@ra.bpsi.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)postgresql(dot)org)
>
-----------------------------------------------------------------------------
david(at)backpack(dot)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!"
-----------------------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Vijay Deval | 2001-03-29 16:20:17 | Re: Query performance question |
Previous Message | Lukas Ertl | 2001-03-29 09:44:41 | Re: Query performance question |