MySQL query optimization

  • Tila: Closed
  • Palkinto: $10
  • Vastaanotetut työt: 7
  • Voittaja: kreshnov

Kilpailun tehtävänanto

I need to optimize a MySQL 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 [login to view URL] 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 [login to view URL] = [login to view URL];

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 [login to view URL] = [login to view URL];

I added this at the end: `AND [login to view URL] > 5` but my boss told me that it's better to use it in the subquery. In the attached files, I show you the EXPLAIN output and the final query that I need.

IN CONCLUSION, I NEED THIS:
1. An optimized SQL query.
2. An explanation of what you did and why it has been optimized (optional but great).

RESTRICTIONS:
1. I can't provide the data set.

Suositellut taidot

Työnantajan palaute

“Professional help!”

Profiilikuva andreshg112, Colombia.

Kilpailun parhaat työt

Näytä lisää töitä

Julkinen selvennystaulu

  • kavipadm
    kavipadm
    • 1 kuukausi sitten

    Which version of mysql are you using

    • 1 kuukausi sitten
    1. andreshg112
      Kilpailun järjestäjä
      • 1 kuukausi sitten

      5.7.12

      • 1 kuukausi sitten
    2. kavipadm
      kavipadm
      • 1 kuukausi sitten

      Hi Sir, can u initiate a chat so that i can send u the query

      • 1 kuukausi sitten
  • Xu Han
    Xu Han
    • 1 kuukausi sitten

    Please reply for my message asap I am a mysql specialist.

    • 1 kuukausi sitten
    1. andreshg112
      Kilpailun järjestäjä
      • 1 kuukausi sitten

      Hi, Xu Han! I haven't received your message on my inbox. Can you ask here in the comments?

      • 1 kuukausi sitten
    2. andreshg112
      Kilpailun järjestäjä
      • 1 kuukausi sitten

      Hi, Xu Han! If you want, you can create a proposal so we can chat.

      • 1 kuukausi sitten

Näytä lisää kommentteja

Kuinka päästä alkuun kilpailuiden kanssa

  • Ilmoita kilpailusi

    Ilmoita kilpailusi Nopeaa ja helppoa

  • Saat valtavasti töitä

    Vastaanota tonnikaupalla osallistumisia Ympäri maailmaa

  • Myönnä palkinto parhaalle työlle

    Myönnä palkinto parhaalle työlle Lataa tiedostot - Helppoa!

Ilmoita kilpailu nyt tai liity tänään!