indexing JSON columns for use with JSON_OVERLAPS

Question

I'm trying to run this query:

SELECT id, duration, type, `from`, `to`, queue_name, created_at
        FROM conference
        WHERE duration >= 60
             AND JSON_OVERLAPS('["6","7","8"]', `groups`)

However, running it is fairly time consuming. Here's the EXPLAIN for that query:

id: 1
select_type: SIMPLE
table: conference
partitions: NULL
type: ALL
possible_key: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1994483
filtered: 33.33
Extra: Using where

The groups column isn't index'd but when I try to add an index with ALTER TABLE conference ADD INDEX groups (groups); I get this error:

Error Code: 3152. JSON column 'groups' supports indexing only via generated columns on a specified JSON path.

I can add a generated column but idk what kind of generated column I could create that'd still let me use JSON_OVERLAPS.

Any ideas?

I'm running MySQL 8.0.32.

Answer 1

Refer to multi-value indexes in the manual.

ALTER TABLE conference
  ADD INDEX ((CAST(`groups` AS UNSIGNED ARRAY)));

EXPLAIN
SELECT id, duration, type, `from`, `to`, queue_name, created_at
        FROM conference
        WHERE duration >= 60
             AND JSON_OVERLAPS('["6","7","8"]', `groups`)\G

Output:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: conference
   partitions: NULL
         type: range
possible_keys: functional_index
          key: functional_index
      key_len: 9
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where