Re: Question about query optimization

On 11/15/06, Matthias.Pitzl@izb.de < Matthias.Pitzl@izb.de> wrote:

Is there any other, and more performat way, to get the last history entry
for a given date than this query?

Create an (independent) index on history_timestamp column and use a min/max in the subquery.

More specifically, your query should look like this:
SELECT    *
FROM    component
JOIN    component_history AS c_h
    USING(component_id)
WHERE    history_timestamp =    (SELECT    max(history_timestamp)
                            FROM    component_history
                            WHERE    c_h.component_id =
                                        component_history.component_id
                            )

Here's a session snippet for an example of how drastically that can reduce the cost and the run-time: