> Data in speed history looks like this:
> interface_id updated_time speed
> 1 2005-08-11 08:10:23 450112
> 1 2005-08-11 10:53:34 501120 <---
> 1 2005-08-11 10:58:11 450112
> 2 2005-08-11 08:10:23 450112 <---
> 2 2005-08-11 11:00:44 350234
> 3 2005-08-11 08:10:23 450112 <---
> The rows of speed_history I want back are marked above with ' <--- '.
>
> Query results should look like:
> interface.interface_id
> interface.link_description
> speed_history.updated_time
> speed_history.speed
The main idea is to join a select of max speeds grouped by interface_id
to the interface table.
If the query runs too slow, then you may get better performance having an
index on (interface_id, speed) and using subselects. In this case
you want to select information about all of interfaces and then have
one of the columns be a subselect that selects one (using limit) speed
from rows that have a matching interface_id ordered by interface_id desc,
speed desc. This combination of limit and order by will be faster than
using max.