Lesson 22 of the SQL Optimization Course: Scenarios for Multi-Valued Indexes

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

DBA
Database developers
Students

We will use MySQL as the demonstration database.

Overview of Multi-Valued Indexes vs. Composite Indexes

Multi-valued indexes and composite indexes serve fundamentally different purposes:

Composite Indexes combine multiple scalar fields (e.g., (r1, r2, r3)), optimized for queries filtering on all specified columns.
Multi-Valued Indexes index array-like data stored in a single JSON field, enabling efficient searches for values within arrays.

Introduced in MySQL 8.0.17, multi-valued indexes solve MySQL’s hard limit of 64 indexes per table while simplifying schema designs.

Case Study: Optimizing Multi-Column Searches

Original Table Structure

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `r1` int DEFAULT NULL,
  `r2` int DEFAULT NULL,
  `r3` int DEFAULT NULL,
  `r4` int DEFAULT NULL,
  `r5` int DEFAULT NULL,
  `r6` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Problem: Slow OR-Based Queries

Querying for a value across 6 columns:

1
2
SELECT COUNT(*) FROM t1 
WHERE r1=650 OR r2=650 OR r3=650 OR r4=650 OR r5=650 OR r6=650;

Execution Time: 210 ms Index Strategy: 6 single-column indexes with INDEX_MERGE.

Simplified Schema with JSON + Multi-Valued Index

1
2
3
4
5
6
7
8
-- Create table with JSON array
CREATE TABLE t3 (
  id INT PRIMARY KEY,
  r_com JSON
);

-- Add multi-valued index
ALTER TABLE t3 ADD KEY idx_r_com ((CAST(r_com->'$.id' AS UNSIGNED ARRAY)));

Optimized Query

1
SELECT COUNT(*) FROM t3 WHERE 650 MEMBER OF(r_com->'$.id');

Execution Time: 200 ms Plan: Uses idx_r_com directly.

Scaling to Extreme Column Counts

Challenge: 500-Column Table

A table with 500 columns (t2) would require 500 single-column indexes to optimize OR queries—exceeding MySQL’s 64-index limit.

Solution: JSON + Multi-Valued Index

1
2
3
4
5
6
7
8
9
-- Convert 500 columns to a JSON array
CREATE TABLE t4 (
  id INT PRIMARY KEY,
  r_com JSON
);

-- Add multi-valued index
ALTER TABLE t4 ADD KEY idx_r_com ((CAST(r_com->'$.id' AS UNSIGNED ARRAY)));

Query Performance

1
SELECT COUNT(*) FROM t4 WHERE 270 MEMBER OF(r_com->'$.id');

Execution Time: 11.31 sec (vs. impractical 500-column index approach)

Key Benefits of Multi-Valued Indexes

Bypass 64-Index Limit: Replace hundreds of columns with one JSON field.
Simplified Queries: Replace verbose OR conditions with MEMBER OF syntax.
Storage Efficiency: Reduce redundant indexes.

👋 See you in the next lesson.

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.