Re: speeding up big query lookup

Silvela, Jaime (Exchange) wrote:
> The obvoious way to get the latest measurement of type A would be to
> join the table against
>
> SELECT object_id, object_val_type_id, max(observation_date)
> FROM object_val
> GROUP BY object_id, object_val_type_id

I'm not sure this is actually the result you want; doesn't this give you
all the unique (object_id, object_val_type_id)'s combined with the max
observation_date in the table (as in, not necessarily related to the
records listed)?

I'd think you want this:
SELECT object_id, object_val_type_id, observation_date
FROM object_val
GROUP BY object_id, object_val_type_id, observation_date
HAVING observation_date = max(observation_date)

Which'd return a single record with the highest observation_date. Though
not strictly necessary, I can imagine you'd want observation_date to be
unique, or you could get grouped observations with the same date.

Although ordering and limiting is probably faster.
I don't think the planner is intelligent enough to know that this would
only return the record with the highest observation_date - it may be
smart enough to reject ("drop from the result set") found records after
finding ones with a higher observation_date (which'd be "interesting"
when using cursors) or something along those lines. Hmm... Now I'm all
curious; an EXPLAIN'd be interesting...

Sorry for the mostly useless post :P

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //