0

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

5
  • Please use EXPLAIN SELECT ... and provide the result so you and others can see and discuss the query plan. Commented Feb 3, 2021 at 8:43
  • @geertjanvdk I have updated Commented Feb 3, 2021 at 8:44
  • 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). Commented Feb 3, 2021 at 9:00
  • @geertjanvdk could you explain for me? Commented Feb 3, 2021 at 9:06
  • 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? Commented Feb 4, 2021 at 13:48

1 Answer 1

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.

12
  • innodb_buffer_pool_size is 20915945472. I can't find any ways to optimize Commented Feb 3, 2021 at 8:40
  • @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
    – Rick James
    Commented Feb 3, 2021 at 17:37
  • I have updated it. Please help me Commented Feb 4, 2021 at 1:22
  • @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.)
    – Rick James
    Commented Feb 4, 2021 at 3:52
  • work_id is optional so only has 4 consts Commented Feb 4, 2021 at 3:59

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.