From: | Jeff Davis <list-pgsql-general(at)dynworks(dot)com> |
---|---|
To: | Dmitry Tkach <dmitry(at)openratings(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: A general database question! |
Date: | 2002-03-21 23:25:19 |
Message-ID: | 200203212329.PAA17841@mail.ucsd.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
> I have a general database design question for you...
> Suppose, I have a database of entities, that have two attributes, say,
> location and name. Location is numerical and name is a varchar.
> I need to be able to search by both location and name (e.g. get all the
> entries whose location is 1 and name is John etc...).
> It is simple so far - I'd just create an an index on both location and
> name...
>
I am not quite understanding why a multi-column index won't work. Can you
explain a little more?
Here is what I was thinking:
create table entity(
type int,
location int,
name varchar(100) not null,
);
create unique index my_index on entity(location,name);
It seems as though an "and" query would run quickly. It also seems as though
if you needed to get a few records that were one of several names, a query
such as the following would run quickly:
select * from entity where location = 5 and (name = 'joe' or name='joseph');
or are the aliases a seperate attribute? I am not clear on exactly where the
alias comes in.
Regards,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2002-03-21 23:36:01 | Re: Newbie question - Which Linux? |
Previous Message | Tom Lane | 2002-03-21 23:02:53 | Re: Postmaster processes running out of control? |
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2002-03-22 01:59:09 | Re: Sequence not updated after a copy ? |
Previous Message | Dmitry Tkach | 2002-03-21 21:33:31 | A bug in gistPageAddItem()/gist_tuple_replacekey() ??? |