Re: efficient storing of urls

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
>
>
>