Re: efficient storing of urls

From: Sean Shanny <shannyconsulting(at)earthlink(dot)net>
To: Shane Wegner <shane-keyword-pgsql(dot)a1e0d9(at)cm(dot)nu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: efficient storing of urls
Date: 2004-02-27 23:00:36
Message-ID: 403FCC14.2040602@earthlink.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Shane,

Can you give an example of a query that has gotten slower due to the
increasing size of the urls table with an explain analyze?

Thanks.

--sean

Shane Wegner wrote:

>Hello list,
>
>I have a database where one of the tables stores urls and
>it's getting to the point where queries are getting quite
>slow. My urls table looks something like:
>
>create table urls(
>id serial,
>url text,
>unique(url),
>primary key(id)
>);
>
>What I am thinking of doing is storing urls in a tree-like
>structure
>
>create table urls(
>id serial,
>url_part text,
>parent_id int, -- references back to urls table
>unique(parent_id,url_part)
>);
>
>So:
>insert into urls (id,parent_id,url_part) (1, NULL,
>'http://www.mydomain.com');
>insert into url (id,parent_id,url_part) values(2, 1, '/images');
>
>url id 2 would represent www.mydomain.com/images without
>actually storing the full hostname and path for each url.
>
>Is this a recommended way of storing urls or is there a
>better way? Is it likely to result in faster joins as each
>row will be smaller?
>
>One final question, how would one get the full url back out
>of the sql table referencing the parent back to the root
>(null parent) for use by an sql like query and would that
>procedure negate any performance benefits by this storage
>method?
>
>Thanks,
>Shane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2004-02-27 23:16:28 Re: efficient storing of urls
Previous Message Shane Wegner 2004-02-27 22:53:54 efficient storing of urls