MySQL slow query with IN() clause on indexed column

Question

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

• NikitaSerbskiy: where did this list of types come from? do you receive it with some query?
• Wilson Hauck: Please post only the first 15 of your IN list from your input file.
• Matteo Riva: I have edited the question to include sample strings that go in the IN() clause. The list comes from code executed before the query, not from the database.
• NikitaSerbskiy: could you please add output of explain command for this select?
• Wilson Hauck: Matteo, Have you considered from KEY type (type) to KEY type (type(60)) USING BTREE - for indexing by prefix of 60 of the 255 possible characters? The backticks may or may not display as entered.

Answer 1

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;

• NikitaSerbskiy: type varchar(255)