Re: efficient storing of urls

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.

What queries?  Do you have indexs on the queried fields?  Can
you please provide the EXPLAIN output from the slow queries?

If you've already looked at all these things, I apologize, if
not, you should look them over before you consider reorganizing
your database.

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


--
Bill Moran
Potential Technologies
http://www.potentialtech.com