With Mysql 5.6.10, I have a table like this:
CREATE TABLE `es_user_action` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(11) unsigned NOT NULL,
`company_id` bigint(11) unsigned NOT NULL,
`work_id` bigint(11) unsigned NOT NULL,
`action` tinyint(2) NOT NULL COMMENT '10, 20, 30, 40',
`action_id` bigint(11) unsigned DEFAULT NULL,
`apply_id` bigint(11) unsigned DEFAULT '0',
`apply_display_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`action_time` datetime NOT NULL,
`scout_time` datetime NOT NULL,
`register_datetime_sorting` datetime DEFAULT NULL,
`score` tinyint(2) DEFAULT '0',
`is_pending` tinyint(2) DEFAULT '0',
`apply_status` tinyint(2) DEFAULT '2' COMMENT '1: paid, 2: free',
`has_response` tinyint(2) DEFAULT '0',
`response_time` datetime DEFAULT NULL,
`is_shown` tinyint(2) DEFAULT '1',
`source` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_company_work` (`user_id`,`work_id`,`company_id`,`apply_id`,`source`),
KEY `IDX_2` (`company_id`,`is_shown`,`apply_status`,`apply_id`,`work_id`,`action_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=436896779 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
The full query I want to perform on the table would be the following:
SELECT
`id` AS `seqNo`,
`company_id` AS `companyId`,
`work_id` AS `workId`,
`action` AS `userActionType`,
`action_time` AS `userActionDatetime`,
`is_pending`,
`apply_status`,
`action_id`,
`source`,
`user_id` AS `userId`,
`apply_display_id`
FROM
`es_user_action`
WHERE
`company_id` = 449664
AND `is_shown` = 1
AND `apply_status` = 1
AND `apply_id` = 0
AND `action_time` >= '2021-01-05 15:56:14'
-- AND `work_id` IN ( 1250160 )
ORDER BY
is_pending ASC,
action ASC,
score DESC,
CASE
source
WHEN "entenshoku" THEN
action_time
END DESC,
CASE
WHEN source <> "entenshoku" THEN
action_time
END ASC
LIMIT 100 OFFSET 0;
The table has around 15 million rows and the following query takes around 15 seconds. Can anyone help out? Thanks in advance.
UPDATED:
This is the explain query result:

UPDATED 4/2/2021:
Handler counts result:

EXPLAIN SELECT ...and provide the result so you and others can see and discuss the query plan.company_id= 449664 ANDaction_time>= '2021-01-05 15:56:14' ANDis_shown= 1 ANDapply_status= 1 ANDapply_id= 0 with index sequence the same as this list?