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