Implementing Coupon Template Retrieval by Product ID in MySQL: Fuzzy Search vs Multi-Value Index

Background: Merchants often use a fuzzy search like WHERE columnName like '%inputName%' to find products, but this can be misused when searching for coupon templates associated with a specific product ID. Two common solutions are ElasticSearch full‑text search or MySQL search; for small data sets, MySQL is simpler.

Solution 1 – Fuzzy Search: Store related product IDs in a column (e.g., relatedProductIds ) and use WHERE related_product_Ids like '%${inputProductId}%' . This method can return false positives (e.g., product ID 11 matches 110, 111, 112). Adding commas before and after the stored IDs and adjusting the query to WHERE related_product_Ids like '%,${inputProductId},%' reduces but does not eliminate errors.

Solution 2 – Multi‑Value Index: MySQL 8.0 supports multi‑value indexes on JSON columns. By storing product IDs as a JSON array (e.g., [110,111,112] ) in a JSON column related_product_ids , you can create a multi‑value index that generates separate index entries for each ID.

What is a Multi‑Value Index?

A normal index maps one row to one index entry; a multi‑value index maps one row to multiple entries, allowing efficient lookup of any element in a JSON array.

How to Use a Multi‑Value Index

1) Create a JSON column: alter table coupon_template add column related_prodcut_ids JSON default NULL;

2) Insert a JSON record: insert into coupon_template(related_product_ids) values ('[110,111,112]');

3) Create the multi‑value index: alter table coupon_template add INDEX relatedProductIdsIndex((cast(json_extract(related_product_ids, '$[*]') as unsigned array)));

The index expression for a JSON array is $[*] ; for a JSON object you would use the appropriate path (e.g., $.ids ).

Querying the Multi‑Value Index

Use MEMBER OF to test membership, e.g.: select * from coupon_template WHERE 110 MEMBER OF (ids_ext->'$.ids');

EXPLAIN shows the query uses the created index ( ids_ext_index ), confirming efficient execution.

Conclusion: MySQL 8.0, released in 2018, introduces JSON types, window functions, multi‑value indexes, and instant online DDL. Since MySQL 5.7 reached end‑of‑life in 2023, migrating to 8.0 is recommended for these advanced features.