SELECT COUNT(*) AS total_count , SUM(CASE WHEN chat_status IN ('hold', 'open') THEN 1 ELSE 0 END) AS open_count , SUM(CASE WHEN chat_status IN ('pending', 'available') THEN 1 ELSE 0 END) AS queue_count , SUM(CASE WHEN chat_status = 'closed' THEN 1 ELSE 0 END) AS closed_count , SUM(CASE WHEN chat_status IN ('available', 'pending') AND active_node_id IS NOT NULL THEN 1 ELSE 0 END) AS pathway_count , SUM(CASE WHEN chat_status IN ('available', 'pending') AND active_survey_response_id IS NOT NULL THEN 1 ELSE 0 END) AS survey_count , group_idFROM text_chatWHERE client_id = 370GROUP BY client_id, group_id;

全表扫描的执行时间是1秒，

mysql> explain SELECT -> COUNT(*) AS total_count -> , SUM(CASE WHEN chat_status IN ('hold', 'open') THEN 1 ELSE 0 END) AS open_count -> , SUM(CASE WHEN chat_status IN ('pending', 'available') THEN 1 ELSE 0 END) AS queue_count -> , SUM(CASE WHEN chat_status = 'closed' THEN 1 ELSE 0 END) AS closed_count -> , SUM(CASE WHEN chat_status IN ('available', 'pending') AND active_node_id IS NOT NULL THEN 1 ELSE 0 END) AS pathway_count -> , SUM(CASE WHEN chat_status IN ('available', 'pending') AND active_survey_response_id IS NOT NULL THEN 1 ELSE 0 END) AS survey_count -> , group_id -> FROM text_chat -> WHERE client_id = 370 -> GROUP BY client_id, group_id;
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | text_chat | ALL | NULL | NULL | NULL | NULL | 998707 | Using where; Using temporary; Using filesort |
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.21 sec)




CREATE TABLE `text_chat` ( `phone_number` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `client_id` mediumint(8) unsigned NOT NULL DEFAULT 0, `group_id` int(10) unsigned DEFAULT NULL, `active_group_id` int(10) unsigned DEFAULT NULL, `client_number` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL, `contact_id` int(10) unsigned DEFAULT NULL, `opened_datetime` datetime DEFAULT NULL, `chat_status` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'closed' COMMENT 'open, closed, hold, pending', `status_datetime` datetime DEFAULT NULL, `active_user_id` int(10) DEFAULT NULL, `active_interaction_id` int(10) unsigned DEFAULT NULL, `active_node_id` int(10) unsigned DEFAULT NULL, `active_survey_response_id` int(10) unsigned DEFAULT NULL, `pathway_completed` tinyint(1) DEFAULT 0, `datetime_last` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`phone_number`,`client_id`), KEY `active_user_id` (`active_user_id`), KEY `chat_status` (`chat_status`), KEY `active_user_id_2` (`active_user_id`,`chat_status`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


mysql> select count(distinct client_id) from text_chat;
+---------------------------+
| count(distinct client_id) |
+---------------------------+
| 712 |
+---------------------------+
1 row in set (0.54 sec)

mysql> select count(distinct group_id) from text_chat;
+--------------------------+
| count(distinct group_id) |
+--------------------------+
| 1476 |
+--------------------------+
1 row in set (0.59 sec)

mysql> select count(*) from text_chat;
+----------+
| count(*) |
+----------+
| 1010692 |
+----------+
1 row in set (0.44 sec)

尝试过的索引

idx_cid(client_id)

idx_gid(group_id)

idx_cid_gid(client_id,group_id)

均没有全表扫描快，请问如何优化或者改写

idx_gid_cid(group_id,client_id)