I have two tables:

definition (7,000 rows):
| definitionId | nameRef  |
|            1 | [1]      |
|            2 | [1,2]    |

name (millions of rows):
| nameId     | name     |
|          1 | Maria    |
|          2 | John     |

nameRef is a JSON column, indexed using:

CREATE INDEX nameRef ON definition ( ( CAST( nameRef AS UNSIGNED ARRAY ) ) );

I'd like to be able to JOIN these tables based on the nameRef, e.g.

FROM definition
JOIN name
ON name.nameId MEMBER OF( definition.nameRef )


SELECT * FROM definition JOIN forename ON name.nameId = JSON_EXTRACT( definition.nameRef, '$[0]' )

However both of these are very slow, as they do not use an index.

Is it possible to make this JOIN using an index?

  • 1
    I tested this but I can't make it use the index either. It will use the index if the query searches for a constant expression, but not if the left argument of MEMBER OF is a column identifier. I wonder if it is simply not implemented. I can't find this restriction in the documentation of the MySQL bug database. Commented Apr 11, 2022 at 14:14
  • 1
    Regardless, I would not use JSON for this task. Instead, I would create a third table to represent the many-to-many relationship between definitions and names, with one row per pairing of definitionId and nameId. That is the normal way to store many-to-many data. Commented Apr 11, 2022 at 14:16
  • Thanks for your help BillKarwin @Ergest Basha. Commented Apr 11, 2022 at 20:11
  • both of these are very slow, as they do not use an index. You need in name (nameId) index primarily... but if the query selects more than 5-7% of rows from name then direct tables scanning is more efffective than index usage. If you want you may try to force the indices usage with according index hints.
    – Akina
    Commented Apr 12, 2022 at 4:33
  • Suggestion by @ErgestBasha is only possible with single-dimension JSON objects; not arrays. Commented Apr 19, 2022 at 20:43


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.