From: walter harms
Date: July 24 2008 12:49pm
Subject: how to optimize: max(timetstamp) where a.foo=b.foo ?
List-Archive: http://lists.mysql.com/mysql/213830
Message-Id: <48887A56.3010103@bfs.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

hi list,
i have tables that look like this( <10.000 entries) :

id,
timestamp,
value


to get the latest value for each id i have queries like:

select * from tab A where timestamp = (select max(timestamp) from tab B where B.id=A.id) group by id ;

on a fast system it takes round 4 sec to complet, but since this is done requlary it slows down to much.
the same query takes on a informix system an a slower computer next to nothing.

any ideas ?

re,
 walter