This is my table:
CREATE TABLE `admin_dash_work_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gameid` varchar(64) NOT NULL DEFAULT '',
`work_id` int(11) NOT NULL,
`date` date NOT NULL,
`watch_num` int(11) NOT NULL,
`like_num` int(11) NOT NULL,
`new_fans_num` int(11) NOT NULL DEFAULT '0',
`update_time` int(11) NOT NULL,
`areaid` varchar(64) NOT NULL DEFAULT '',
`average_viewer_count` int(11) NOT NULL DEFAULT '0',
`peak_viewer_count` int(11) NOT NULL DEFAULT '0',
`video_duration_sec` int(11) NOT NULL DEFAULT '0',
`share_num` int(11) NOT NULL DEFAULT '0',
`comment_num` int(11) NOT NULL DEFAULT '0',
`expo_num` int(11) NOT NULL DEFAULT '-1',
`watch_seconds` int(11) NOT NULL DEFAULT '0',
`submitted_time` bigint(20) NOT NULL DEFAULT '0',
`channel_type` int(11) NOT NULL DEFAULT '0',
`init_video_play_num` int(11) NOT NULL DEFAULT '0',
`init_video_like_num` int(11) NOT NULL DEFAULT '0',
`init_video_share_num` int(11) NOT NULL DEFAULT '0',
`init_video_comment_num` int(11) NOT NULL DEFAULT '0',
`stream_start_time` bigint(20) NOT NULL DEFAULT '0',
`released_time` bigint(20) NOT NULL DEFAULT '0',
`audit_status` int(11) NOT NULL DEFAULT '0',
`streamer_task_id` int(11) NOT NULL DEFAULT '0',
`uid` varchar(128) NOT NULL DEFAULT '',
`video_url` varchar(255) NOT NULL DEFAULT '',
`user_country` varchar(255) NOT NULL DEFAULT '',
`user_region` varchar(255) NOT NULL DEFAULT '',
`admin_task_id` int(11) NOT NULL DEFAULT '0',
`applied_time` bigint(20) NOT NULL DEFAULT '0',
`joined_time` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `work_id-date` (`work_id`,`date`) USING BTREE,
KEY `idx_submitted_time` (`submitted_time`),
KEY `idx_channel_type` (`channel_type`),
KEY `idx_audit_status` (`audit_status`),
KEY `idx_streamer_task_id` (`streamer_task_id`),
KEY `idx_uid` (`uid`),
KEY `idx_user_country` (`user_country`),
KEY `idx_user_region` (`user_region`),
KEY `idx_admin_task_id` (`admin_task_id`),
KEY `idx_applied_time` (`applied_time`),
KEY `idx_joined_time` (`joined_time`),
KEY `idx_game_area` (`gameid`,`areaid`),
KEY `idx_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=2901623 DEFAULT CHARSET=utf8mb4;
Here are my index stats for admin_dash_work_record:
enter image description here
| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Visible |
|---|---|---|---|---|---|---|
| 0 | PRIMARY | 1 | id | A | 2445905 | YES |
| 0 | work_id-date | 1 | work_id | A | 169665 | YES |
| 0 | work_id-date | 2 | date | A | 2445905 | YES |
| 1 | idx_submitted_time | 1 | submitted_time | A | 161810 | YES |
| 1 | idx_channel_type | 1 | channel_type | A | 6 | YES |
| 1 | idx_audit_status | 1 | audit_status | A | 5 | YES |
| 1 | idx_streamer_task_id | 1 | streamer_task_id | A | 67426 | YES |
| 1 | idx_uid | 1 | uid | A | 15947 | YES |
| 1 | idx_user_country | 1 | user_country | A | 61 | YES |
| 1 | idx_user_region | 1 | user_region | A | 8 | YES |
| 1 | idx_admin_task_id | 1 | admin_task_id | A | 1953 | YES |
| 1 | idx_applied_time | 1 | applied_time | A | 51928 | YES |
| 1 | idx_joined_time | 1 | joined_time | A | 1 | YES |
| 1 | idx_game_area | 1 | gameid | A | 1 | YES |
| 1 | idx_game_area | 2 | areaid | A | 1 | YES |
| 1 | idx_date | 1 | date | A | 923 | YES |
And this is my sql：
explain SELECT
dash_work.admin_task_id,
COUNT(distinct dash_work.streamer_task_id) AS join_num,
SUM(dash_work.watch_num) AS watch_num,
SUM(dash_work.like_num) AS like_num,
...
dash_work.channel_type
FROM(
SELECT
work_id,
ANY_VALUE(admin_task_id) AS admin_task_id,
ANY_VALUE(streamer_task_id) AS streamer_task_id,
ANY_VALUE(channel_type) AS channel_type,
ANY_VALUE(video_url) AS video_url,
SUM(watch_num) + ANY_VALUE(init_video_play_num) AS watch_num,
SUM(like_num) + ANY_VALUE(init_video_like_num) AS like_num,
SUM(share_num) + ANY_VALUE(init_video_share_num) AS share_num,
SUM(comment_num) + ANY_VALUE(init_video_comment_num) AS comment_num,
SUM(
CASE
WHEN date >= '2021-12-14 00:00:00' THEN watch_num
ELSE 0
END
) AS incr_watch_num,
SUM(
CASE
WHEN date >= '2021-12-14 00:00:00' THEN like_num
ELSE 0
END
) AS incr_like_num,
SUM(
CASE
WHEN date >= '2021-12-14 00:00:00' THEN share_num
ELSE 0
END
) AS incr_share_num,
SUM(
CASE
WHEN date >= '2021-12-14 00:00:00' THEN comment_num
ELSE 0
END
) AS incr_comment_num,
SUM(new_fans_num) as new_fans_num,
SUM(average_viewer_count * video_duration_sec) AS average_viewer_count_sum,
MAX(peak_viewer_count) AS peak_viewer_count,
MAX(average_viewer_count) AS average_viewer_count,(
case
when ANY_VALUE(channel_type) in (1, 2, 3, 5, 6) then SUM(video_duration_sec)
else 0
end
) as video_duration_sec,(
case
when ANY_VALUE(channel_type) in (4, 7, 9, 8, 10) then SUM(video_duration_sec)
else 0
end
) as live_duration_sec,
CAST(ANY_VALUE(stream_start_time) AS CHAR) AS stream_start_time,
CAST(ANY_VALUE(released_time) AS CHAR) AS released_time
FROM
admin_dash_work_record force INDEX (
`work_id-date`,
`idx_submitted_time`,
`idx_admin_task_id`,
`idx_channel_type`,
`idx_user_region`,
`idx_game_area`
)
WHERE
date <= '2023-12-19 23:59:59' AND (
gameid = '7'
AND areaid = 'asia'
)
AND (
submitted_time BETWEEN UNIX_TIMESTAMP('2021-12-14 00:00:00')
AND UNIX_TIMESTAMP('2023-12-19 23:59:59')
AND audit_status = 1
)
AND channel_type in (1, 5, 6, 8, 9)
AND user_region in (
'Africa'
)
GROUP BY
`work_id`
ORDER BY admin_task_id, channel_type asc
) AS dash_work
GROUP BY
dash_work.admin_task_id,
dash_work.channel_type
And this is explain result:
enter image description here
When I run dash_work's build sql alone, it's fast. At that time, when external nested searches were added, it became slower.
So Why Mysql do not create index in subquery?
And How can I add index in subquery?
Maybe there are other optimization methods that have been changed, please tell me.