MySQL: Slow GROUP BY with INNER JOIN

Question

I have 2 tables to join.

Table 1: helpdesk_tickets

CREATE TABLE `helpdesk_tickets` (
  `ticket_idx` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(150) DEFAULT NULL,
  `bot_id` varchar(150) DEFAULT NULL,
  `user_id` varchar(150) DEFAULT NULL,
  `ticket_status` varchar(60) DEFAULT NULL,
  `ticket_assignee_id` varchar(150) DEFAULT NULL,
  `chat_log_id_start` varchar(150) DEFAULT NULL,
  `chat_log_id_end` varchar(150) DEFAULT NULL,
  `chat_id` varchar(150) NOT NULL,
  `creation_date` datetime DEFAULT NULL,
  `ticket_number` varchar(50) DEFAULT NULL,
  `ticket_group` varchar(300) DEFAULT NULL,
  `additional_information` varchar(600) DEFAULT NULL,
  `ticket_priority` varchar(20) DEFAULT NULL,
  `ticket_category` varchar(50) DEFAULT NULL,
  `department_id` varchar(50) DEFAULT NULL,
  `department_idx` int(11) DEFAULT NULL,
  `workspace_id` varchar(50) DEFAULT NULL,
  `workspace_idx` int(11) DEFAULT NULL,
  PRIMARY KEY (`ticket_idx`),
  KEY `user_id` (`user_id`),
  KEY `bot_id` (`bot_id`),
  KEY `comp_1` (`chat_log_id_start`,`chat_log_id_end`),
  KEY `workspace_id` (`workspace_id`),
  KEY `creation_date` (`creation_date`),
  KEY `idx_helpdesk_tickets_ticket_idx` (`ticket_idx`),
  KEY `chat_id` (`chat_id`),
  KEY `ticket_id` (`ticket_id`)
) ENGINE=InnoDB AUTO_INCREMENT=604745 DEFAULT CHARSET=latin1;

Table 2: chat_logs

CREATE TABLE `chat_logs` (
  `chat_log_idx` int(11) NOT NULL AUTO_INCREMENT,
  `chat_log_id` varchar(50) NOT NULL DEFAULT '0',
  `bot_id` varchar(50) DEFAULT NULL,
  `user_id` varchar(150) DEFAULT NULL,
  `message` mediumtext,
  `creation_date` datetime DEFAULT NULL,
  `message_from` varchar(10) NOT NULL,
  `chat_type` varchar(20) NOT NULL,
  `chat_status` varchar(20) NOT NULL,
  `chat_id` varchar(150) NOT NULL,
  `message_id` varchar(100) DEFAULT NULL,
  `message_timestamp` varchar(50) DEFAULT NULL,
  `message_status` varchar(50) DEFAULT NULL,
  `message_last_update` datetime DEFAULT NULL,
  `broadcast_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`chat_log_idx`),
  KEY `user_id` (`user_id`),
  KEY `bot_id` (`bot_id`),
  KEY `message_from` (`message_from`),
  KEY `creation_date` (`creation_date`),
  KEY `chat_log_id` (`chat_log_id`),
  KEY `message_last_update` (`message_last_update`),
  KEY `message_id` (`message_id`),
  KEY `chat_type` (`chat_type`),
  KEY `chat_id` (`chat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14852380 DEFAULT CHARSET=latin1;

I have around 80k rows in helpdesk_tickets and 16k rows in chat_logs.

Running this query

SELECT 
    chat_type, COUNT(ticket_id) AS total_tickets
FROM
    helpdesk_tickets AS ht
        INNER JOIN
    chat_logs AS cl ON cl.chat_id = ht.chat_id
GROUP BY chat_type;

takes very-very long time. It takes more than 10 minutes. It takes very long I never got to see the results because I always stopped the execution.

EXPLAIN gives me this

+----+-------------+-------+------------+-------+-------------------+---------+---------+------------------------+-------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys     | key     | key_len | ref                    | rows  | filtered | Extra                        |
+----+-------------+-------+------------+-------+-------------------+---------+---------+------------------------+-------+----------+------------------------------+
|  1 | SIMPLE      | ht    | NULL       | index | chat_id           | chat_id | 152     | NULL                   | 15870 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | cl    | NULL       | ref   | chat_type,chat_id | chat_id | 152     | botmasterdb.ht.chat_id |  1838 |   100.00 | NULL                         |
+----+-------------+-------+------------+-------+-------------------+---------+---------+------------------------+-------+----------+------------------------------+

Running this query alone without join is very fast, 0.04 seconds.

SELECT 
    chat_type
FROM
    chat_logs
GROUP BY chat_type;

The reason why I'm joining those 2 tables is to count how many tickets for each chat_type.

user_1766831 · Answer

Also... You say COUNT(ticket_id) instead of COUNT(*). This forces the execution to check each ticket_id for being NOT NULL. I suspect that is over-kill. Do some or all of:

Change the definition of ticked_id (and many other columns) to NOT NULL, _if appropriate
Change to COUNT(*)
Add the index already suggested: INDEX(chat_id, ticket_id)

user_13895572 · Answer

I'm not sure why your have created so many indexes. My sugguestion is to analysis and reduce your indexes according to your business.

The following are how to optimized the query in this thread. Mysql version is 5.7.31 in my verification sandbox.

Add conbination index on botika_chat_logs table:

KEY idx_type_id (`chat_type`, `chat_id`)

Add conbination index on botika_helpdesk_tickets table:

KEY `index_chat_id_ticket_id` (`chat_id`, `ticket_id`)

Change query to:

SELECT
    chat_type, COUNT(ticket_id) AS total_tickets
FROM
    botika_chat_logs AS cl
        INNER JOIN
    botika_helpdesk_tickets AS ht ON cl.chat_id = ht.chat_id
GROUP BY chat_type;

The explain result is as following:

+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys           | key                     | key_len | ref             | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | cl    | NULL       | index | idx_type_id             | idx_type_id             | 174     | NULL            |    1 |   100.00 | Using index |
|  1 | SIMPLE      | ht    | NULL       | ref   | index_chat_id_ticket_id | index_chat_id_ticket_id | 152     | test.cl.chat_id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+-----------------+------+----------+-------------+

The choice of a driving table made using many factors. Table sizes, cardinality of column values, and other things can affect the choice of a driving table (eg: HINTS). More details about how to choose driving table, please refer to this doc

However, when I test the query above using different join order, the execution plan result is defferent. I think it may be caused by no data in my own sandbox. The execution plan is as following. That why I still give you that query in Step 3.

mysql> explain SELECT
    ->     chat_type, COUNT(ticket_id) AS total_tickets
    -> FROM
    ->     botika_helpdesk_tickets AS ht
    ->         INNER JOIN
    ->     botika_chat_logs AS cl ON cl.chat_id = ht.chat_id
    -> GROUP BY chat_type;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    24
Current database: test

+----+-------------+-------+------------+------+-------------------------------+---------+---------+-----------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys                 | key     | key_len | ref             | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+-------------------------------+---------+---------+-----------------+------+----------+---------------------------------+
|  1 | SIMPLE      | ht    | NULL       | ALL  | chat_id                       | NULL    | NULL    | NULL            |    1 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | cl    | NULL       | ref  | chat_type,chat_id,idx_id_type | chat_id | 152     | test.ht.chat_id |    1 |   100.00 | NULL                            |
+----+-------------+-------+------------+------+-------------------------------+---------+---------+-----------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.01 sec)

The table sizes, cardinality of column values and indexed in your tables must be different with that on my sandbox. Be sure to check execution plan on your sandbox.

Why the query will be faster, after adding those indexes:

botika_chat_logs have less records than botika_helpdesk_tickets. The performance will be better if botika_chat_logs is the driving table.
Index index_chat_id_ticket_id is a covering index, which contains ticket_id and chat_id. When counting ticket_id, mysql will not need to return primary index.
New created index idx_type_id is a covering index, which will let query have better performance.
Group by chat_type will benifit from New created index idx_type_id too.

MySQL: Slow GROUP BY with INNER JOIN

2 Answers