Обсуждение: Query questions

Hi list,

i have a problem with creating a query and i hope somebody can give me
some hints. I have the following table
pid(varchar), crit(varchar), val1(varchar), val2(varchar),
iDate(timestamp)
where there are up to 63 million lines with 1500 distinct pids and
around 42000 distinct crits:
pid   crit   val1   val2   iDate
'yyy'  'aaa'  'b'    'c'      someTime
'yyy'  'bbb'  'b'    'a'      anotherTime
...
What i have to do is to export the table for which i have to query the
table with the following pattern: select val1, val2 from mytable where
pid='yyy' and crit='aaa'. But if i do this 63 million times, it just
takes too long. So, what i would like to do is to make a query where i
would get all val1 and val2 values for ONE pid and ALL crit. The problem
here is that all crit values have to be ordered by the iDate column. So
i tried to select all distinct crits ordered by iDate: select distinct
crit, iDate from mytable order by iDate. The problem here is that not
only the distinct crits are selected but also the distinct iDate values,
which in the end retrieves much more lines than there are distinct crit
values in the table.

I hope somebody can help me with this probably rather trivial problem.
Thanks!
Chris

am  31.07.2006, um 15:32:19 +0200 mailte Christian Rengstl folgendes:
> Hi list,
>
> i have a problem with creating a query and i hope somebody can give me
> some hints. I have the following table
> pid(varchar), crit(varchar), val1(varchar), val2(varchar),
> iDate(timestamp)
> where there are up to 63 million lines with 1500 distinct pids and
> around 42000 distinct crits:
> pid   crit   val1   val2   iDate
> 'yyy'  'aaa'  'b'    'c'      someTime
> 'yyy'  'bbb'  'b'    'a'      anotherTime
> ...
> What i have to do is to export the table for which i have to query the
> table with the following pattern: select val1, val2 from mytable where
> pid='yyy' and crit='aaa'. But if i do this 63 million times, it just
> takes too long. So, what i would like to do is to make a query where i

Do you have indexe on pid and crit?
Can you paste a "explain analyse select val1, val2 from mytable where
pid='yyy' and crit='aaa';"

Which version? ("select version();").


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===