MySQL slow query with IN() clause on indexed column

MySQLperformance

I have a table with this structure:

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `playhead` int(10) unsigned NOT NULL,
  `payload` longtext COLLATE utf8_unicode_ci NOT NULL,
  `metadata` longtext COLLATE utf8_unicode_ci NOT NULL,
  `recorded_on` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_5387574AD17F50A634B91FA9` (`uuid`,`playhead`),
  KEY `type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=16944996 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I'm running queries with a long list of type in a IN() clause, like:

SELECT COUNT(id) FROM events WHERE type IN (<long_list_of_types)

where "long list" means around 80 type which are strings like these:

Namespace.Domain.User.Event.SomeEvent1Happened
Namespace.Domain.User.Event.SomeEvent2Happened
Namespace.Domain.User.Event.SomeEvent3Happened
Namespace.Domain.User.Event.SomeEvent4Happened
Namespace.Domain.User.Event.SomeEvent5Happened
Namespace.Domain.User.Event.SomeEvent6Happened
Namespace.Domain.User.Event.SomeEvent7Happened
Namespace.Domain.User.Event.SomeEvent8Happened
Namespace.Domain.User.Event.SomeEvent9Happened
Namespace.Domain.User.Event.SomeEvent10Happened
Namespace.Domain.User.Event.SomeEvent11Happened
...

Result of the query:

| COUNT(id) |
+-----------+
|   8941096 |
+-----------+
1 row in set (27.77 sec)

This is the output of EXAPLAIN on that query:

| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows  | Extra                    |
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | events | range | type          | type | 768     | NULL | 38400 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+

The table contains roughly 16 million events, and the query takes just short of 30 seconds. Is there a way to improve execution time on this kind of query?

MySQL version 5.6.44

Best Answer

You may try a temporary table and join against that:

create temporary table t1 
( type varchar(255) not null
);

insert into t1 (type) values ('Namespace.Domain.User.Event.SomeEvent1Happened');
insert into t1 (type) values ('Namespace.Domain.User.Event.SomeEvent2Happened');
insert into t1 (type) values ('Namespace.Domain.User.Event.SomeEvent3Happened');
...

create index x1 on t1 (type);

SELECT COUNT(id) 
FROM events e 
JOIN t1
    ON e.type = t1.type;