Multi-Valued Indexes in MySQL

What Are They?

Normal Index

  • Argentina
  • Chile
  • Denmark
  • France
  • Poland

Multi-Valued Index

  • ["banana", "apple"]
  • ["orange, "apple"]
  • ["plum", "banana"]

What are they used for?

JSON columns!

🤔 "But I can already index data from a JSON column."

Previously you could only index a single value from a JSON column.

What kinds of queries are they used for?

These indexes can be used for queries that use the following JSON functions:
 

(We'll discuss the latter two)

JSON_CONTAINS()

  • Check if a JSON value contains all provided items
     
  • You can search for a single item or an array of items

JSON_OVERLAPS()

  • Check if a JSON value contains any provided items

Creating a Multi-Valued Index

ALTER TABLE posts
ADD INDEX tags( (CAST(metadata->'$.tags' AS char(60) ARRAY)) )

Combine `CAST` with the same JSON path you will use in your queries:

Use in composite indexes

ALTER TABLE posts
ADD INDEX tags_composite(
	category,
	owner_id,
	(CAST(metadata->'$.tags' AS char(60) ARRAY))
)

Caveats

Be sure to read the documentation thoroughly, because there are caveats/restrictions:

 

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

Caveats

Among others:

 

  • Arrays cannot contain`null` items
  • Only one per composite index
  • Cannot be used as primary key or for ordering
  • Limited character sets
Made with Slides.com