SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    What good are one-liners if they don't
    fit.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    What good are one-liners if they don't
    fit.

  4. #4
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    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 ;
    Code:
    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 ;
    Code:
    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 ;
    What good are one-liners if they don't
    fit.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    change this --
    Code:
    KEY `gps_unit_id` (`gps_unit_id`),
    to this --
    Code:
    KEY `gps_unit_id` (`gps_unit_id` , id ),
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    KEY `created_gps_unit_id_idx` (`created`,`gps_unit_id`)
    does this mean I'm better of sorting the query by date?
    What good are one-liners if they don't
    fit.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Hieronymus View Post
    does this mean I'm better of sorting the query by date?
    i haven't seen your query yet, so i can't say

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    change this --
    Code:
    KEY `gps_unit_id` (`gps_unit_id`),
    to this --
    Code:
    KEY `gps_unit_id` (`gps_unit_id` , id ),
    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?
    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.

    Code:
    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 ;-) ) What are the drawbacks of these kind of double indeces?
    What good are one-liners if they don't
    fit.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ;-)
    What good are one-liners if they don't
    fit.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •