Re: speeding up big query lookup

On fös, 2006-08-25 at 18:34 -0400, Silvela, Jaime (Exchange) wrote:
> This is a question on speeding up some type of queries.
>
> I have a very big table that catalogs measurements of some objects over
> time. Measurements can be of several (~10) types. It keeps the
> observation date in a field, and indicates the type of measurement in
> another field.
>
> I often need to get the latest measurement of type A for object X.
> The table is indexed by object_id.

one popular way is to create a composite index:
CREATE INDEX object_val_id_type_date
ON object_val(object_id,
object_val_type_id,
observation_date);

then you could

SELECT * FROM object_val
WHERE object_id=?
AND object_val_type_id=?
ORDER BY observation_date DESC
LIMIT 1;

Hope this helps
gnari