mysqlsqlnormalization

Is there a way to index on multiple values in a column in MySQL

Currently I have a column in my table which has a set of comma separated values. I am currently using it to filter the results. I am wondering if it would be possible to index on it and query directly using it.

My table is as below:

userId        types
123           A, B, C
234           B, C

If I want to query a user which has types A and C, should get 123 If with B and C then 123, 234

EDIT: I am aware the problem can be solved by normalization. However my table is actually storing json and this field is a virtual column referencing a list. there are no relations used anywhere. We are facing an issue where querying by types was not considered and is now causing performance impact

Solution

  • MySQL supports Multi-Value Indexes for JSON columns as of MySQL 8.0.17. It seems like exactly your case.

    Details: https://dev.mysql.com/doc/refman/8.3/en/create-index.html#create-index-multi-valued