From: Jeff Davis <list-pgsql-general@dynworks.com>
To: Dmitry Tkach <dmitry@openratings.com>
To: pgsql-general@postgresql.org
Subject: Re: A general database question!
Date: Thu, 21 Mar 2002 15:25:19 -0800
Message-ID: <200203212329.PAA17841@mail.ucsd.edu> (raw)
In-Reply-To: <3C9A4759.6000607@openratings.com>
References: <3C9A4759.6000607@openratings.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: list-pgsql-general@dynworks.com, dmitry@openratings.com, pgsql-general@postgresql.org
Subject: Re: A general database question!
In-Reply-To: <200203212329.PAA17841@mail.ucsd.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox