How to improve performance in searching for latest record corresponding to a unit

Databases
1

Hi,

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

2

views won’t help (and by the way, views don’t go stale when new rows are added)

what kinds of indexes have you defined? could you do a SHOW CREATE TABLE please

3

hi,

If you want to try to optimize the query you need to have the complete database design since the search for let’s say the latest location or latest track state (ignition on/off) or latest FMS message needs a lott of the tables.
I guess there are a few points to gain, but overall it will still be slow since sorting by date or grouping is just slow.

I was wandering if my proposed sollution is weird or bad practice or so.

4

one part of the query that seems particually slow is:

SELECT
MAX(id) AS id, gps_unit_id
FROM
fms1
GROUP BY gps_unit_id

the results of this subquery get joined with all the units belonging to a relation. So I guess those two can be combined in one faster query.
I want to have the latest FMS message per unit belonging to a relation


CREATE TABLE IF NOT EXISTS `fms1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `gps_unit_id` int(10) unsigned NOT NULL,
  `odometer` double default NULL,
  `total_fuel` float default NULL,
  `engine_hours` float default NULL,
  `actual_speed` float default NULL,
  `actual_engine_speed` float default NULL,
  `actual_engine_torque` int(11) default NULL,
  `kickdown_switch` tinyint(1) default NULL,
  `accelerator_position` float default NULL,
  `brake_switch` tinyint(1) default NULL,
  `clutch_switch` tinyint(1) default NULL,
  `cruise_active` tinyint(1) default NULL,
  `pto_active` tinyint(1) default NULL,
  `fuel_level` float default NULL,
  `engine_temperature` int(11) default NULL,
  `turbo_pressure` float default NULL,
  `axle_weight_0` float default NULL,
  `axle_weight_1` float default NULL,
  `axle_weight_2` float default NULL,
  `axle_weight_3` float default NULL,
  `service_distance` int(11) default NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `gps_unit_id` (`gps_unit_id`),
  KEY `created_gps_unit_id_idx` (`created`,`gps_unit_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2673414 ;




CREATE TABLE IF NOT EXISTS `gps_units` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `relation_id` int(10) unsigned default NULL,
  `session_id` int(10) unsigned default NULL,
  `telephone` varchar(16) default NULL,
  `numberplate` varchar(12) default NULL,
  `country_id` int(10) unsigned default NULL,
  `uid` varchar(64) default NULL,
  `name` varchar(128) default NULL,
  `descr` varchar(255) default NULL,
  `brand` varchar(64) default NULL,
  `model` varchar(64) default NULL,
  `type` varchar(64) default NULL,
  `version` varchar(64) default NULL,
  `device_token` varchar(256) default NULL,
  `icon_id` int(10) NOT NULL default '1',
  `icon_type` tinyint(5) unsigned NOT NULL default '0',
  `deleted` tinyint(1) NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `relation_id` (`relation_id`),
  KEY `session_id` (`session_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=356 ;



CREATE TABLE IF NOT EXISTS `relations` (
  `id` int(11) NOT NULL auto_increment,
  `name` char(255) default NULL,
  `address` char(255) default NULL,
  `postalcode` char(20) default NULL,
  `city` char(255) default NULL,
  `country_id` int(11) default NULL,
  `telephone` char(20) default NULL,
  `fax` char(20) default NULL,
  `email` char(255) default NULL,
  `website` char(255) default NULL,
  `kvknumber` char(20) default NULL,
  `license_type` tinyint(4) default NULL,
  `public` tinyint(1) NOT NULL default '0',
  `deleted` tinyint(1) NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=260 ;
5

change this –

KEY `gps_unit_id` (`gps_unit_id`),

to this –

KEY `gps_unit_id` (`gps_unit_id` [COLOR="#0000FF"], id [/COLOR]),

you’ll see a marked improvement because this is now a covering index for that query

by the way, what is the purpose of the relations table?

6

thanks, I’ll give it a try tomorrow. ‘relations’ is bit awkwardly chosen (historical reasons), you can see it more as a company or account. Under relations you have users, units, locations and so on.

at the moment the fms1 table also has

KEY `created_gps_unit_id_idx` (`created`,`gps_unit_id`)

does this mean I’m better of sorting the query by date?

7

i haven’t seen your query yet, so i can’t say

:slight_smile:

8

Wow, that works! In MySQL workbench the subquerie was near instant, on my test server the whole query (see below) went from 1.6s ~ 2.1s to about ~70ms.


SELECT GpsUnit.id AS unit_id, GpsUnit.name, GpsUnit.type, MAX(GpsPoint.date) AS date, 
            GpsTrackSegment.id, 
            GpsUnitIcon.url AS icon, GpsUnit.telephone AS telephone, GpsTrack.ignition AS ignition,
            GpsUnit.icon_type AS icon_type, GpsUnit.numberplate AS numberplate, GpsUnit.relation_id,
            latest_gps_points.date AS latest_gps_point_date,
            FMS1.odometer, FMS1.fuel_level
            FROM latest_gps_points 
            INNER JOIN gps_points AS GpsPoint ON latest_gps_points.gps_point_id = GpsPoint.id      
            INNER JOIN gps_track_segments AS GpsTrackSegment ON GpsTrackSegment.id = GpsPoint.gps_track_segment_id 
            INNER JOIN gps_tracks AS GpsTrack ON GpsTrack.id = GpsTrackSegment.gps_track_id 
            INNER JOIN gps_units AS GpsUnit ON GpsUnit.id = GpsTrack.gps_unit_id 
            LEFT JOIN gps_unit_icons AS GpsUnitIcon ON GpsUnitIcon.id = GpsUnit.icon_id
            LEFT JOIN (
                SELECT MAX( id ) AS id, gps_unit_id
                FROM fms1
                GROUP BY gps_unit_id
            ) AS fms1_tmp ON fms1_tmp.gps_unit_id = GpsUnit.id
            LEFT JOIN fms1 AS FMS1 ON FMS1.id = fms1_tmp.id
            WHERE GpsUnit.relation_id = $relation_id
            GROUP BY GpsTrack.gps_unit_id 
            ORDER BY GpsUnit.name ASC

query gets latest ignition status (from the tracks table) and the latest FMS1 message for fuel and mileage. for all units belonging to a relation. Not all units send these fms1 messages.

Offcourse I’m happy this works, but I don’t really understand it. Can you explain it a bit (or point me to the corresponding chapter in your book :wink: ) What are the drawbacks of these kind of double indeces?

9

i’m not sure i can adequately explain it in one short forum post

did you do a search for covering index yet?

drawback? a composite index is bigger than an index on a single column

10

I did look at the first few google results. I think I got some sort of idea what is going on, but it’s difficult to say if I can spot obvious places where I can apply this.
I do have a big book about database design that still needs to be read :wink: