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