how to improve performance in searching for latest record corresponding to a unit
I'm storing for every GPS unit a GPS point, a FMS1 message and a FMS4 message. There are various queries where I need the most recent gps point, (and/or) message per unit or every unit belonging to a single user (relation).
The problem is that these queries all are very slow (the now take more then 1 sometimes 2 seconds)
I tried many things, including the use of views (which doesn't realy help since new points and messages get submitted every 30s per unit)
I'm now thinking to not have a view, but an actual table with latest GPS point and one with the messages. The API just saves the points twice. Once in the gps_points table and an UPDATE for the record corresponding to the unit. This record thus always holds the latest gps point. It can even duplicate the data. Nicer would be to first save the GPS point or message and then save it's ID in the latest table.
I'm a bit lost for ideas, so I'm curious to know if this to far out of the box, or do you think this is a viable sollution that creates very low overhead and eliminates the use of the heavy sorting and joining queries.
Thx for your ideas