MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE trigram (
    id BIGINT(20) NOT NULL,
    trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (trigram, id) USING BTREE,
    INDEX id (id) USING BTREE
)
COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;
EXPLAIN SELECT id FROM trigram 
 WHERE trigram IN ('dba', 'ba.', 'a.s', '.st', 'sta', 'tac', 'ack')
 GROUP BY ID HAVING COUNT(*) = 7


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
trigram
null
index
PRIMARY,id
id
8
null
1
100.00
Using where; Using index


EXPLAIN
    SELECT 
        id
    FROM
        trigram
            JOIN
        (SELECT 'dba' AS trigram UNION ALL SELECT 'ba.' UNION ALL SELECT 'a.s' UNION ALL SELECT '.st' UNION ALL SELECT 'sta' UNION ALL SELECT 'tac' UNION ALL SELECT 'ack') tr1 ON trigram.trigram = tr1.trigram
    GROUP BY ID
    HAVING COUNT(*) = 7


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
trigram
null
index
PRIMARY,id
id
8
null
1
100.00
Using index; Using temporary

1
PRIMARY
<derived2>
null
ALL
null
null
null
null
7
14.29
Using where; Using join buffer (hash join)

2
DERIVED
null
null
null
null
null
null
null
null
null
No tables used

3
UNION
null
null
null
null
null
null
null
null
null
No tables used

4
UNION
null
null
null
null
null
null
null
null
null
No tables used

5
UNION
null
null
null
null
null
null
null
null
null
No tables used

6
UNION
null
null
null
null
null
null
null
null
null
No tables used

7
UNION
null
null
null
null
null
null
null
null
null
No tables used

8
UNION
null
null
null
null
null
null
null
null
null
No tables used

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	trigram	null	index	PRIMARY,id	id	8	null	1	100.00	Using index; Using temporary
1	PRIMARY	<derived2>	null	ALL	null	null	null	null	7	14.29	Using where; Using join buffer (hash join)
2	DERIVED	null	null	null	null	null	null	null	null	null	No tables used
3	UNION	null	null	null	null	null	null	null	null	null	No tables used
4	UNION	null	null	null	null	null	null	null	null	null	No tables used
5	UNION	null	null	null	null	null	null	null	null	null	No tables used
6	UNION	null	null	null	null	null	null	null	null	null	No tables used
7	UNION	null	null	null	null	null	null	null	null	null	No tables used
8	UNION	null	null	null	null	null	null	null	null	null	No tables used