Re: SELECT syntax question - combining COUNT and DISTINCT

On 1 Oct 2003 at 9:55, Cath Lawrence <Cath.Lawrence@ wrote:

>
> >
> > A secondary question (relating to my quality control problem but not
> > the subject heading) is why I can't do this:
> > "SELECT DISTINCT chain_id FROM chain WHERE pdb_id IN (SELECT pdb_code
> > FROM pdb_entry WHERE chain_count=1);"
> > This looks OK but takes apparently forever (or until I lose patience)
> > on my Mac G4. There are about 10,000 values in that subquery, so I
> > didn't expect it to be lightning fast, but it seems ridiculous.
Try indexing the column in the where clause

> >
> >
> > Relevant snippets of table:
> > pdb_entry - 19,587 entries
> > ---------
> > pdb_code is KEY, CHAR(4), unique, indexed
> > chain_count is INTEGER
> >
> > chain - 40,844 entries
> > -----
> > pdb_id is CHAR(4) Foreign key, indexed
> > chain_id is CHAR(1)
> >
> >
> >
> > Cath Lawrence, Cath(dot)Lawrence(at)anu(dot)edu(dot)au
> > Senior Scientific Programmer, Centre for Bioinformation Science,
> > John Curtin School of Medical Research (room 4088)
> > Australian National University, Canberra ACT 0200
> > ph: (02) 61257959 mobile: 0421-902694 fax: (02) 61252595
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>