> i have a table with around 57 million tuples, with the following columns: pid(varchar), crit(varchar), val1(varchar),
val2(varchar).Example:
> pid crit val1 val2
> p1 c1 x y
> p1 c2 x z
> p1 c3 y x
> ...
> What i am doing is to query all val1 and val2 for one pid and all crit values:
>
> select val1, val2, crit from mytable where pid='somepid' and crit in(select crit from myCritTable);
> where myCritTable is a table that contains all crit values (around 42.000) ordered by their insertion date.
In case myCritTable doesn't change a lot and this select by contrast is
executed a lot, have you considered precomputing whether a record from
your big table has a crit value from myCritTable?
Of course this info would be invalidated each time myCritTable is
updated, so you would trade fast selects on the big table vs. slow
updates on myCritTable. Don't know wether that makes sence for you...
Bye, Chris.