Optimizing SQL query from derived table

I have the following SQL query

select s.channel_id, count(1) as unique_views
from (
    select m.channel_id
    from view_statistics vs
    inner join medias m on m.id = vs.media_id
    group by m.channel_id, vs.session_id
) s
group by s.channel_id

that selects from this table view_statistics (which stores millions of rows, narrowed down to ~2M here for testing purposes):

CREATE TABLE `view_statistics` ( 
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `media_id` int(10) unsigned NOT NULL,
    `file_id` int(10) unsigned NOT NULL,
    `session_id` int(10) unsigned NOT NULL,
    `browser_id` int(10) unsigned NOT NULL,
    `device_id` int(10) unsigned NOT NULL,
    `operating_system_id` int(10) unsigned NOT NULL,
    `country_id` int(10) unsigned NOT NULL,
    `datetime_from` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `datetime_to` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `datetime_yearly` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `datetime_monthly` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `datetime_weekly` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `datetime_daily` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `datetime_hourly` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `hits` int(10) unsigned NOT NULL,
    `bytes` bigint(20) unsigned NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    KEY `view_statistics_datetime_hourly_index` (`datetime_hourly`), 
    KEY `view_statistics_session_id_datetime_hourly_index` (`session_id`,`datetime_hourly`), 
    KEY `view_statistics_datetime_daily_index` (`datetime_daily`),
    KEY `view_statistics_session_id_datetime_daily_index` (`session_id`,`datetime_daily`),
    KEY `view_statistics_datetime_weekly_index` (`datetime_weekly`),
    KEY `view_statistics_session_id_datetime_weekly_index` (`session_id`,`datetime_weekly`),
    KEY `view_statistics_datetime_monthly_index` (`datetime_monthly`),
    KEY `view_statistics_session_id_datetime_monthly_index` (`session_id`,`datetime_monthly`),
    KEY `view_statistics_datetime_yearly_index` (`datetime_yearly`),
    KEY `view_statistics_session_id_datetime_yearly_index` (`session_id`,`datetime_yearly`),
    KEY `view_statistics_media_id_foreign` (`media_id`),
    KEY `view_statistics_file_id_foreign` (`file_id`),
    KEY `view_statistics_browser_id_foreign` (`browser_id`),
    KEY `view_statistics_device_id_foreign` (`device_id`),
    KEY `view_statistics_operating_system_id_foreign` (`operating_system_id`),
    KEY `view_statistics_country_id_foreign` (`country_id`),
    CONSTRAINT `view_statistics_browser_id_foreign` FOREIGN KEY (`browser_id`) REFERENCES `statistic_browsers` (`id`),
    CONSTRAINT `view_statistics_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `statistic_countries` (`id`),
    CONSTRAINT `view_statistics_device_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `statistic_devices` (`id`),
    CONSTRAINT `view_statistics_file_id_foreign` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`),
    CONSTRAINT `view_statistics_media_id_foreign` FOREIGN KEY (`media_id`) REFERENCES `medias` (`id`),
    CONSTRAINT `view_statistics_operating_system_id_foreign` FOREIGN KEY (`operating_system_id`) REFERENCES `statistic_operating_systems` (`id`),
    CONSTRAINT `view_statistics_session_id_foreign` FOREIGN KEY (`session_id`) REFERENCES `statistic_sessions` (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=1366805 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

And medias

CREATE TABLE `medias` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` binary(16) NOT NULL,
  `channel_id` int(10) unsigned NOT NULL,
  `folder_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned DEFAULT NULL,
  `language_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `validated` tinyint(1) NOT NULL DEFAULT 0,
  `published` tinyint(1) NOT NULL DEFAULT 0,
  `streams` tinyint(3) unsigned DEFAULT NULL,
  `duration` decimal(7,2) DEFAULT NULL,
  `client` enum('http','cli') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `http_ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `http_user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cli_user` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cli_hostname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `debug` tinyint(1) NOT NULL DEFAULT 0,
  `v1` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Imported from V1',
  `collected_at` timestamp NULL DEFAULT NULL,
  `ready_at` timestamp NULL DEFAULT NULL,
  `published_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `discarded_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `uniq` binary(32) GENERATED ALWAYS AS (unhex(sha2(concat_ws('|',`folder_id`,`name`,ifnull(`deleted_at`,0)),256))) STORED,
  PRIMARY KEY (`id`),
  UNIQUE KEY `medias_uuid_unique` (`uuid`),
  UNIQUE KEY `medias_uniq_unique` (`uniq`),
  KEY `medias_channel_id_foreign` (`channel_id`),
  KEY `medias_folder_id_foreign` (`folder_id`),
  KEY `medias_category_id_foreign` (`category_id`),
  KEY `medias_language_id_foreign` (`language_id`),
  KEY `medias_ready_at_index` (`ready_at`),
  KEY `medias_discarded_at_index` (`discarded_at`),
  CONSTRAINT `medias_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
  CONSTRAINT `medias_channel_id_foreign` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`),
  CONSTRAINT `medias_folder_id_foreign` FOREIGN KEY (`folder_id`) REFERENCES `folders` (`id`),
  CONSTRAINT `medias_language_id_foreign` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1036 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

Other related tables (browsers,device, operating_system, etc.) are listing tables, nothing fancy.

and altought the query is simple to me, it runs over many seconds with the following explain:

id  select_type     table       type    possible_keys                    key                                key_len ref         rows    Extra
1   PRIMARY         <derived2>  ALL     NULL                             NULL                               NULL    NULL        649152  Using temporary; Using filesort
2   DERIVED         m           index   PRIMARY                          medias_channel_id_foreign          4       NULL        1008    Using index; Using temporary; Using filesort
2   DERIVED         vs          ref     view_statistics_media_id_foreign view_statistics_media_id_foreign   4       vod.m.id    644 

I don’t see how I can optimize this so simple query at all, can anyone point me to the right direction ?

Answer

An equivalent query would be:

select m.channel_id, count(distinct vs.session_id) as unique_views
from view_statistics vs
inner join medias m on m.id = vs.media_id
group by m.channel_id

Ideal indexes for this query would probably be medias(channel_id, id) (already exists) and view_statistics(media_id, session_id). However – It might already improve the performance with the given indexes.