2

I need to optimize a MySQL (5.7.12) query. I've been reading the docs about optimization in MySQL docs but It's been hard for me to comprehend it. I have two tables:

vehicles

CREATE TABLE `vehicles` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`metadata` json DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`license_plate` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`vehicle_type` varchar(205) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`brand` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`model` year(4) DEFAULT NULL,
`color` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`fuel_type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`agency_id` int(10) unsigned DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`vehicle_line` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`initial_odometer` double DEFAULT NULL,
`reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`fuel_chip` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`engine_displacement` smallint(5) unsigned DEFAULT NULL,
`driver_data` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `vehicles_code_unique` (`code`),
KEY `vehicles_agency_id_foreign` (`agency_id`),
CONSTRAINT `vehicles_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1561 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

positions

CREATE TABLE `positions` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`vehicle_id` int(10) unsigned NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`speed` decimal(8,2) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`alarm` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`altitude` double DEFAULT NULL,
`direction` double DEFAULT NULL,
`metadata` json DEFAULT NULL,
`time` datetime DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`event_type` smallint(5) unsigned DEFAULT NULL,
`address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `positions_vehicle_id_time_deleted_at_index` (`vehicle_id`,`time`,`deleted_at`),
KEY `positions_time_index` (`time`),
KEY `speed` (`speed`),
CONSTRAINT `positions_vehicle_id_foreign` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=105581942 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

As you can see, I have 1561 records on vehicles and 105581942 on positions. I need to get the vehicles with its latest position which speed is over 5, in the same row. That's determined by the positions.time field, not by created_at or id. I have the next SQL optimized, it gets the latest position per vehicle:

SELECT a.*
FROM positions a
INNER JOIN
(
SELECT vehicle_id, MAX(time) mxdate
FROM positions
GROUP BY vehicle_id
) b
ON a.vehicle_id = b.vehicle_id
AND a.time = b.mxdate;

EXPLAIN output for quick query

MySQL responds very quickly with it, but if I add speed to the subquery, "It never ends":

SELECT a.*
FROM positions a
INNER JOIN
(
SELECT vehicle_id, MAX(time) mxdate
FROM positions
where speed > 5
GROUP BY vehicle_id
) b
ON a.vehicle_id = b.vehicle_id
AND a.time = b.mxdate;

EXPLAIN output for slow query

I added this at the end: AND a.speed > 5 but my boss told me that it's better to use it in the subquery.

In the end, I need query shown above but I think optimizing the innermost query is enough.

select v.*,
       lastlocations.speed,
       lastlocations.latitude,
       lastlocations.longitude,
       lastlocations.time,
       lastlocations.event_type
from vehicles v,
     (
         SELECT a.*
         FROM positions a
                  INNER JOIN
              (
                  SELECT vehicle_id, MAX(time) mxdate
                  FROM positions
                  GROUP BY vehicle_id
              ) b ON a.vehicle_id = b.vehicle_id
                  AND a.time = b.mxdate
     ) lastlocations
where v.id = lastlocations.vehicle_id;

UPDATE: After creating an index like this: positions_speed_vehicle_id_time_index (speed, vehicle_id, time), the EXPLAIN output looks better: enter image description here

0

2 Answers 2

2
  • Shrink the table size to help with performance.
  • DOUBLE takes 8 bytes; FLOAT takes 4. FLOAT provides 1.7 meter resolution, sufficient for vehicles.
  • Do you use created_at and updated_at?
  • Do you fill in the address for every entry? Seems like a waste of time (and space).
  • Consider storing only actions where moving > 5. (Is that km/hr?)
  • Consider a third table for the 'current' location; this gets updated on every input record. It would be quite small (1561 rows).
  • Your 105M positions is probably growing quite rapidly. There will be other performance issues in the near future, other than this one SELECT.
  • What other queries will soon need optimizing?
  • Do you use positions.id for anything? Whether or not you do, I may have further optimizations.
2
  • I use time to get more recent positions. If I locally change double by float, I'm getting the same output executing explain expanded. Jun 17, 2019 at 19:31
  • * I don't fill in the address for every row. It's just searched on Google when needed. * We have to store actions < 5 km/hr (kph) because we need to know when the vehicles were stopped. * We have the current locations on Redis but we have to know what was the last position before the vehicle stops. Jun 17, 2019 at 20:29
0

Ok, I've been oriented to find a way.

After creating an index like this: positions_speed_vehicle_id_time_index (speed, vehicle_id, time), the EXPLAIN output looks better: enter image description here

Here it is the explanation (I received in an image): enter image description here

I would like a more optimized query, but that's what I get right now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.