3

I need to run the following query on my production database, using 2 tables that contain more than 10M rows each :

SELECT rm.parent_id as processed_media_id
FROM render_medias rm
JOIN processed_medias pm ON pm.id = rm.parent_id
WHERE rm.id IN (
    SELECT MAX(rm.id) AS id
    FROM render_medias rm
    WHERE rm.parent_type = 'App\\Models\\ProjectMedia'
    AND rm.deleted_at IS NULL
    GROUP BY rm.parent_id
)
AND rm.parent_type = 'App\\Models\\ProjectMedia'
AND (JSON_VALUE(rm.data, '$.keepSize') = 'blur' or JSON_VALUE(rm.data, '$.legacyKeepSize') = 'true')
AND pm.transformations IS NULL

Right now, the query is going timeout. Here is the EXPLAIN output :

+----+-------------+-------+------------+------+---------------------------------------------------------------------------+-------------------------------------------+---------+----------------------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                                                             | key                                       | key_len | ref                  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------------------------------------------------------------+-------------------------------------------+---------+----------------------+----------+----------+-------------+
|  1 | PRIMARY     | pm    | NULL       | ALL  | PRIMARY                                                                   | NULL                                      | NULL    | NULL                 | 22595880 |    10.00 | Using where |
|  1 | PRIMARY     | rm    | NULL       | ref  | render_medias_parent_type_parent_id_index,render_medias_parent_type_index | render_medias_parent_type_parent_id_index | 5       | const,playplay.pm.id |        1 |   100.00 | Using where |
|  2 | SUBQUERY    | rm    | NULL       | ref  | render_medias_parent_type_parent_id_index,render_medias_parent_type_index | render_medias_parent_type_parent_id_index | 1       | const                | 13642578 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------------------------------------------------------------------+-------------------------------------------+---------+----------------------+----------+----------+-------------+
3 rows in set, 1 warning (0,02 sec)

Is there something I can optimize to make it usable ?

here is the description of the 2 related tables :

CREATE TABLE `render_medias` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int unsigned NOT NULL,
  `parent_type` enum('App\\Models\\ProjectMedia','App\\Models\\RawMediaV2') COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('to_process','processing','processed','errored','canceled') COLLATE utf8mb3_unicode_ci NOT NULL,
  `folder` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `rendered_url` text COLLATE utf8mb3_unicode_ci,
  `thumbnail_url` text COLLATE utf8mb3_unicode_ci,
  `to_queue_data` json DEFAULT NULL,
  `data` json DEFAULT NULL,
  `render_duplicated_id` int unsigned DEFAULT NULL,
  `launched_at` timestamp NULL DEFAULT NULL,
  `rendered_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `raw_metadata` json DEFAULT NULL,
  `urls` json DEFAULT NULL,
  `normalized_file_metadata` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `render_medias_render_duplicated_id_foreign` (`render_duplicated_id`),
  KEY `render_medias_parent_type_parent_id_index` (`parent_type`,`parent_id`),
  KEY `render_medias_status_index` (`status`),
  KEY `render_medias_parent_type_index` (`parent_type`),
  CONSTRAINT `render_medias_render_duplicated_id_foreign` FOREIGN KEY (`render_duplicated_id`) REFERENCES `render_medias` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=45691332 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE `processed_medias` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned DEFAULT NULL,
  `raw_media_id` int unsigned NOT NULL,
  `filter_id` bigint unsigned DEFAULT NULL,
  `speed_id` int unsigned DEFAULT NULL,
  `source` enum('pexels','tenor','upload','storyblocks','getty','getty_edito','unsplash','giphy','favorites','recently_used','library','option_list','music_list') COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `relation_type` tinyint unsigned NOT NULL DEFAULT '0',
  `last_render_media_id` int unsigned DEFAULT NULL,
  `transformations` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `project_medias_project_id_foreign` (`project_id`),
  KEY `project_medias_raw_media_v2_id_foreign` (`raw_media_id`),
  KEY `project_medias_relation_type_index` (`relation_type`),
  KEY `processed_medias_filter_id_foreign` (`filter_id`),
  KEY `processed_medias_last_render_media_id_foreign` (`last_render_media_id`),
  KEY `processed_medias_speed_id_foreign` (`speed_id`),
  CONSTRAINT `processed_medias_filter_id_foreign` FOREIGN KEY (`filter_id`) REFERENCES `processed_medias_filters` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `processed_medias_last_render_media_id_foreign` FOREIGN KEY (`last_render_media_id`) REFERENCES `render_medias` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `processed_medias_speed_id_foreign` FOREIGN KEY (`speed_id`) REFERENCES `processed_medias_speeds` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `project_medias_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `project_medias_raw_media_v2_id_foreign` FOREIGN KEY (`raw_media_id`) REFERENCES `raw_medias` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=25564774 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
1
  • Which database system...MySQL?
    – J.D.
    Commented May 7 at 12:44

1 Answer 1

0

It may be faster to use a window function rather than a self-join.

SELECT rm.parent_id as processed_media_id
FROM (
    SELECT rm.*,
      ROW_NUMBER() OVER (PARTITION BY rm.parent_id ORDER BY rm.id DESC) AS rn
    FROM render_medias rm
    WHERE rm.parent_type = 'App\\Models\\ProjectMedia'
      AND rm.deleted_at IS NULL
) rm
JOIN processed_medias pm ON pm.id = rm.parent_id
WHERE rm.rn = 1
  AND (JSON_VALUE(rm.data, '$.keepSize') = 'blur' OR JSON_VALUE(rm.data, '$.legacyKeepSize') = 'true')
  AND pm.transformations IS NULL;

You may want to push the JSON_VALUE predicates into the subquery, but that may change semantics.

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.