Josh Berkus wrote:
> Joseph,
>
>
>>select d.field1, d.field2, u.field1, u.field2, (select ml.field from
>>mltable where ml.key1
>>= u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from
>>utable u, dtable d,
>>where u.key1 = d.key order by d.somefield limit 25 offset ???;
>>
>
>>From the look of it, you want to simply take the utable references out
> of the subselect. Try:
>
> SELECT d.field1, d.field2, u.field1, u.field2, mll.latest
> FROM utable u, dtable d,
> (SELECT key1, key2, max(serial) as latest
> FROM mltable
> GROUP BY key1, key2) mll
> WHERE u.key1 = d.key
> AND u.key1 = mll.key1 and u.key2 = mll.key2
> ORDER BY d.somefield
> LIMIT 25 OFFSET $pageno
>
> That should give you the utable and dtable records, plus the last serial
> value while executing the subselect only once per query call. Give it a
> spin.
>
OK I tried a variant on this of doing select ml.field from (sublect) mll WHERE
ml.field
in(values) and mll.serial = ml.serial. I am now doing two queries, but the second one
is
a simple index query that returns rather fast. The first, however, is horendous. I
have
thousands of entries in the utable, and this subquery is getting all thoses thousands
of
corresponding max(serial) values from the mltable before throwing all but the 25 that
this
query needs away. I didn't spell out exactly how all my data is aranged so you didn't
know this would happend, but otherwise it might have worked. Or maybe postgres should
have been smart enough to eliminate the other values before it did the group by
> -Josh
>
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology [EMAIL PROTECTED]
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
