Mysql slow query on large table

Question

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: explain query

UPDATED 4/2/2021:

Handler counts result:

handler counts

• geertjanvdk: Please use EXPLAIN SELECT ... and provide the result so you and others can see and discuss the query plan.
• Thai Nguyen Hung: @geertjanvdk I have updated
• geertjanvdk: Focus on the the 'filesort', so the sort buffer might be too small. But, yeah, your query might need some tweaking indeed. (just want to make sure the query plan is added).
• Thai Nguyen Hung: @geertjanvdk could you explain for me?
• Wilson Hauck: Have you considered, WHERE company_id = 449664 AND action_time >= '2021年01月05日 15:56:14' AND is_shown = 1 AND apply_status = 1 AND apply_id = 0 with index sequence the same as this list?

Answer 1

IDX_2 is the best index for that query when the test for work_id is included. The ORDER BY is too complex for any index to help.

If you need to exclude work_id, then make an index without it.

The table is probably under 2GB; how big is innodb_buffer_pool_size?

Most of the BIGINTs are likely to be overkill. Shrinking them from that 8-byte size would shrink the table. But that probably would not help the 15 seconds much.

• Thai Nguyen Hung: innodb_buffer_pool_size is 20915945472. I can't find any ways to optimize
• Rick James: @ThaiNguyenHung - Let's get a little more insight into what is going on. Use this to get the Handler counts: mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts
• Thai Nguyen Hung: I have updated it. Please help me
• Rick James: @ThaiNguyenHung - Ah; I did not notice that the test on work_id was commented out. I made changes to my Answer. (The EXPLAIN showed 4 "const", I was expecting 5.)
• Thai Nguyen Hung: work_id is optional so only has 4 consts